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

Solution 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.

Solution 2:

i tried this query and it seems to be fine:

select 
obj.type REFERENCING_OBJECT_TYPE
 ,SCHEMA_NAME(obj.schema_id) REFERENCING_OBJECT_SCHEMA
 ,obj.name                  REFERENCING_OBJECT_NAME
from sysdepends x
INNER JOIN sys.objects obj ON x.id  = obj.object_id
where depid = object_id('yourSchema.yourTable')
and col_name(depid, depnumber) = 'yourColumn'

Solution 3:

Best ways to do it are described in this article.

A sample:

SELECT OBJECT_NAME (referencing_id),
              referenced_database_name, 
       referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies d
WHERE OBJECT_NAME(d.referenced_id) = 'TableName'
      AND OBJECT_DEFINITION (referencing_id)  LIKE '%ColumnName%'
ORDER BY OBJECT_NAME(referencing_id);