In SQL Server, how can I find everywhere a column is referenced?

Question

Within my rather large database, I would like to find out everywhere a column is referenced within the entire schema (SPs, functions, tables, triggers...). I don't want to just do a text search since this will pick up comments and also will find similarly named columns from other tables.

Does anyone know if/how I can do this? I use SQL Server 2008.

Answer 1

Warning: Even though this is a text-search method, the script I'm going to share has saved me lots and lots of hours. It searches inside:

• scalar functions
• table-valued functions
• stored procedures
• views
• triggers

I needed to specify a collation to make it work for me.

SELECT     sys.objects.object_id,      sys.schemas.name AS [Schema],      sys.objects.name AS Object_Name,      sys.objects.type_desc AS [Type] FROM sys.sql_modules (NOLOCK)  INNER JOIN sys.objects (NOLOCK) ON sys.sql_modules.object_id = sys.objects.object_id  INNER JOIN sys.schemas (NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id WHERE     sys.sql_modules.definition COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%{Column Name}%' ESCAPE '\' ORDER BY sys.objects.type_desc, sys.schemas.name, sys.objects.name 

The output is like the following:

Update: If you need to search for a certain table, SP, etc. you could use a more specialized query:

DECLARE @SCHEMA_NAME VARCHAR(100) = 'dbo'; DECLARE @OBJECT_NAME VARCHAR(100) = 'MY_OBJECT';  SELECT     sys.objects.object_id,     sys.schemas.name AS [Schema],      sys.objects.name AS Object_Name,      sys.objects.type_desc AS [Type] FROM sys.sql_modules (NOLOCK)  INNER JOIN sys.objects (NOLOCK) ON sys.sql_modules.object_id = sys.objects.object_id  INNER JOIN sys.schemas (NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id WHERE     (            '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]'+@SCHEMA_NAME+'.'+@OBJECT_NAME+'[^a-z_]%' ESCAPE '\'         OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]\['+@SCHEMA_NAME+'\].'+@OBJECT_NAME+'[^a-z_]%' ESCAPE '\'         OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]'+@SCHEMA_NAME+'.\['+@OBJECT_NAME+'\][^a-z_]%' ESCAPE '\'         OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]\['+@SCHEMA_NAME+'\].\['+@OBJECT_NAME+'\][^a-z_]%' ESCAPE '\'     ) ORDER BY sys.objects.type_desc, sys.schemas.name, sys.objects.name 

P.S.: Both queries search inside comments too.