CODEWITHSUNDEEP
sql-server
Aaron
Aaron

Reputation: 433

Search for column in entire SQL database

I need to change a column's data type but I do not every location in the database that might be referencing it. Is there a script or application that can search all views, stored procedures, functions, table computed columns, etc. within a database and show what is referencing the column? I am using SQL Server 2005 Standard.

Thanks, Aaron

Upvotes: 0

Views: 5417

Answers (4)

David Atkinson
David Atkinson

Reputation: 5899

I work for Red Gate and I see that SQL Search as already been mentioned. Glad that works for you. Another tool that can specifically list column dependencies is SQL Prompt 5, due to be released soon. You can download the EA build by visiting: http://www.surveymk.com/s.aspx?sm=zDJogAY5rwdIwOX/SqtTCQ%3d%3d and joining the early access list. I'd welcome you to try this out and let me know if it doesn't match your requirements. Another great feature it has is the ability to list your invalid objects. In other words, if you rename a column and you have a stored procedure that references the old column, it will draw your attention to this.

Upvotes: 0

Mark SQLDev
Mark SQLDev

Reputation: 539

I like using a free search add-in tool from redgate software. I'm amazed at how useful it is - you can find all references to text quickly with it.

This description is from SQL Curry:

SQL Search finds fragments of SQL text within stored procedures, functions, views and more and once you find them, it quickly allows you to click and jump to the objects, wherever they happen to be on your servers. It’s pretty cool!

Here is the link: SQL Search

Upvotes: 2

marc_s
marc_s

Reputation: 754368

You can always inspect the sys.columns catalog view:

SELECT  
    c.NAME 'Col Name',
    OBJECT_NAME(c.OBJECT_ID) 'Table Name',
    t.name
FROM
    sys.columns c
INNER JOIN 
    sys.types t ON c.system_type_id = t.system_type_id
WHERE
    c.Name = 'your-column-name-here'

and based on that information, you can generate the ALTER statements for a database:

SELECT  
    'ALTER TABLE dbo.' + OBJECT_NAME(c.OBJECT_ID) +
    ' ALTER COLUMN ' + c.NAME ' NewDataType NULL'
FROM
    sys.columns c
WHERE
    c.Name = 'your-column-name-here'

This query generates a set of ALTER TABLE .... statements which you can then copy to a SSMS query window and execute.

Word of warning: if any of the columns are being referenced - in a foreign key relationship, or if there's a default or check constraint on them - this approach might fail. In that case, you'd need to do some extra steps for those columns (like drop the constraints first etc.)

Update: this searches for the columns as defined in tables.

If you need to search into stored procedures, view and functions as well, I would strongly recommend using Red-Gate's excellent and free (!!) SQL Search utility - excellent stuff!

Upvotes: 6

pavanred
pavanred

Reputation: 13803

This query will help you find any table's column and the column it is referring to - 

SELECT OBJECT_NAME(f.parent_object_id) AS [Table], COL_NAME(fc.parent_object_id,fc.parent_column_id) AS [Column],
OBJECT_NAME (f.referenced_object_id) AS RefTable,     COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS RefColumn, 
f.name AS FK

FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc 
ON f.OBJECT_ID = fc.constraint_object_id

WHERE OBJECT_NAME(f.parent_object_id) = '<your table name>'
AND COL_NAME(fc.parent_object_id,fc.parent_column_id) = '<your column name>'

Upvotes: 0

Related Questions