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

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.

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' 

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); 

Does not show if there is a reference to a temporary table
Example

create table dbo.TableName (columnName int ) go create procedure dbo.ProcedureOne as update dbo.TableName set columnName = 1 go create or alter procedure dbo.ProcedureTwo as create table #test (dd int) update t1 set t1.columnName = 1 from dbo.TableName t1 inner join #test t2 on t1.columnName = t2.dd SELECT 1 As Level ,t2.type AS ObjectType ,CAST(CONCAT(SCHEMA_NAME(t2.schema_id),'.',t2.name) AS varchar(256)) AS RefName ,CAST('' AS varchar(256)) AS RefBy FROM SYSDEPENDS t1 LEFT JOIN SYS.OBJECTS t2 ON t1.id = t2.OBJECT_ID WHERE t1.depid = OBJECT_ID('dbo.TableName') AND COL_NAME(t1.depid, t1.depnumber) = 'columnName' 

example

As Luv said this is an old question but I've found two more solutions that may be helpful.

I'm using the sys.dm_sql_referenced_entities system object that finds all referenced objects and columns in a specified object. You can use the following query:

SELECT DISTINCT referenced_schema_name AS SchemaName, referenced_entity_name AS TableName, referenced_minor_name AS ColumnName FROM sys.dm_sql_referenced_entities ('yourrefencingobject', 'OBJECT'); GO 

Which gives the following result:

Downside of this object is that you'll need to specify a referencing object.

Or do a search like:

SELECT DISTINCT object_name(id) FROM AdventureWorks2012.dbo.syscomments (nolock) WHERE text like '%BusinessEntityID%' 

Which gives the following result:

I've also find the following SP that you could use in this article, but haven't tested it properly:

> DECLARE @string varchar(1000), @ShowReferences char(1) > > SET @string = 'Person.Person.BusinessEntityID' --> searchstring > > SET @ShowReferences = 'N' > /****************************************************************************/ /* > */ /* TITLE: sp_FindReferences */ /* */ /* DATE: 18 February, 2004 */ /* */ /* AUTHOR: WILLIAM MCEVOY */ /* */ /****************************************************************************/ /* > */ /* DESCRIPTION: SEARCH SYSCOMMENTS FOR INPUT STRING, OUTPUT NAME OF OBJECT */ /* > */ /****************************************************************************/ set nocount on > > declare @errnum int , > @errors char(1) , > @rowcnt int , > @output varchar(255) > > select @errnum = 0 , > @errors = 'N' , > @rowcnt = 0 , > @output = '' > > /****************************************************************************/ /* INPUT DATA VALIDATION > */ /****************************************************************************/ > > > /****************************************************************************/ /* M A I N P R O C E S S I N G > */ /****************************************************************************/ > > -- Create temp table to hold results DECLARE @Results table ( Name varchar(55), Type varchar(12), DateCreated datetime, > ProcLine varchar(4000) ) > > > IF (@ShowReferences = 'N') BEGIN insert into @Results select > distinct > 'Name' = convert(varchar(55),SO.name), > 'Type' = SO.type, > crdate, > '' > from sysobjects SO > join syscomments SC on SC.id = SO.id where SC.text like '%' + @string + '%' union select distinct > 'Name' = convert(varchar(55),SO.name), > 'Type' = SO.type, > crdate, > '' > from sysobjects SO where SO.name like '%' + @string + '%' union select distinct > 'Name' = convert(varchar(55),SO.name), > 'Type' = SO.type, > crdate, > '' > from sysobjects SO > join syscolumns SC on SC.id = SO.ID where SC.name like '%' + @string + '%' order by 2,1 END ELSE BEGIN insert into @Results > select > 'Name' = convert(varchar(55),SO.name), > 'Type' = SO.type, > crdate, > 'Proc Line' = text > from sysobjects SO > join syscomments SC on SC.id = SO.id where SC.text like '%' + @string + '%' union select > 'Name' = convert(varchar(55),SO.name), > 'Type' = SO.type, > crdate, > 'Proc Line' = '' > from sysobjects SO where SO.name like '%' + @string + '%' union select > 'Name' = convert(varchar(55),SO.name), > 'Type' = SO.type, > crdate, > 'Proc Line' = '' > from sysobjects SO > join syscolumns SC on SC.id = SO.ID where SC.name like '%' + @string + '%' order by 2,1 END > > IF (@ShowReferences = 'N') BEGIN select Name, > 'Type' = Case (Type) > when 'P' then 'Procedure' > when 'TR' then 'Trigger' > when 'X' then 'Xtended Proc' > when 'U' then 'Table' > when 'C' then 'Check Constraint' > when 'D' then 'Default' > when 'F' then 'Foreign Key' > when 'K' then 'Primary Key' > when 'V' then 'View' > else Type > end, > DateCreated > from @Results > order by 2,1 END ELSE BEGIN select Name, > 'Type' = Case (Type) > when 'P' then 'Procedure' > when 'TR' then 'Trigger' > when 'X' then 'Xtended Proc' > when 'U' then 'Table' > when 'C' then 'Check Constraint' > when 'D' then 'Default' > when 'F' then 'Foreign Key' > when 'K' then 'Primary Key' > when 'V' then 'View' > else Type > end, > DateCreated, > ProcLine > from @Results > order by 2,1 END 

Hope this helps

I would like to avoid using sys.sql_dependencies because this feature will be removed in a future version of Microsoft SQL Server. I also cannot use OBJECT_DEFINITION function because all my objects are encrypted. So I came up with the following query which seems pretty simple and works for me so well that I even wrapped it to the function:

-- ============================================= -- Description: Gets all the stored procedures, functions and triggers referencing the specified column. -- Example: SELECT * FROM dbo.UFN_GET_SP_FN_TR_REFERENCING_COLUMN(N'dbo', N'MY_TABLE', N'MY_COLUMN'); -- ============================================= CREATE FUNCTION dbo.UFN_GET_SP_FN_TR_REFERENCING_COLUMN ( @SchemaName sysname, @TableName sysname, @ColumnName sysname ) RETURNS TABLE AS RETURN SELECT R.referencing_schema_name + N'.' + R.referencing_entity_name AS referencing_entity_name FROM sys.dm_sql_referencing_entities(@SchemaName + N'.' + @TableName, 'OBJECT') AS R INNER JOIN sys.objects AS O ON R.referencing_id = O.object_id WHERE O.[type] IN ('FN'/*SQL scalar function*/, 'IF'/*SQL inline table-valued function*/, 'TF'/*SQL table-valued-function*/, 'P'/*SQL Stored Procedure*/, 'TR' /*SQL DML trigger*/) AND EXISTS(SELECT 1 FROM sys.dm_sql_referenced_entities (R.referencing_schema_name + N'.' + R.referencing_entity_name, 'OBJECT') AS RE WHERE RE.referenced_entity_name = @TableName AND RE.referenced_minor_name = @ColumnName); GO 

Hello Although this is an old post I was able to combine few tips from above got something like this below which was help full for me. The reason had I create this one is the column I was in lot of tables so it did not provide me clear output.

SELECT SCHEMA_NAME(schema_id)+'.'+[name] as objectname ,type_desc ,referenced_schema_name AS SchemaName ,referenced_entity_name AS TableName ,referenced_minor_name AS ColumnName FROM [sys].[all_objects] ob cross apply sys.dm_sql_referenced_entities ( SCHEMA_NAME(schema_id)+'.'+[name], 'OBJECT') e where is_ms_shipped = 0 and type_desc in ('AGGREGATE_FUNCTION' ,'SQL_SCALAR_FUNCTION' ,'SQL_INLINE_TABLE_VALUED_FUNCTION' ,'SQL_STORED_PROCEDURE' ,'SQL_TABLE_VALUED_FUNCTION' ,'SQL_TRIGGER' ,'VIEW') and name !='sp_upgraddiagrams' and referenced_entity_name = 'table name' and referenced_minor_name = 'columnname' 

Here's a slight tweak on @alex's TV-UDF to include views also:

/* Source: https://stackoverflow.com/a/47775531/852956 Gets all the stored procedures, functions and triggers referencing the specified column. SELECT * FROM Utility.ft_SelectSprocFuncAndTrigrRefs(N'BrakeRotor', N'BrakeRotors', N'BrakeRotorNumber'); */ CREATE FUNCTION Utility.ft_SelectSprocFuncAndTrigrRefs ( @SchemaName sysname, @TableName sysname, @ColumnName sysname ) RETURNS TABLE AS RETURN SELECT QUOTENAME(R.referencing_schema_name) + N'.' + QUOTENAME(R.referencing_entity_name) AS ReferencingEntityName FROM sys.dm_sql_referencing_entities(@SchemaName + N'.' + @TableName, 'OBJECT') AS R INNER JOIN sys.objects AS O ON R.referencing_id = O.object_id WHERE O.[type] IN ( 'FN'/*SQL scalar function*/, 'IF'/*SQL inline table-valued function*/, 'TF'/*SQL table-valued-function*/, 'P'/*SQL Stored Procedure*/, 'TR' /*SQL DML trigger*/ ) AND EXISTS( SELECT 1 FROM sys.dm_sql_referenced_entities (R.referencing_schema_name + N'.' + R.referencing_entity_name, 'OBJECT') AS RE WHERE RE.referenced_entity_name = @TableName AND RE.referenced_minor_name = @ColumnName) UNION SELECT QUOTENAME(VIEW_SCHEMA) + N'.' + QUOTENAME(VIEW_NAME) AS ReferencingEntityName FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE WHERE TABLE_SCHEMA = @SchemaName AND TABLE_NAME = @TableName AND column_name = @ColumnName GO 

Had to do this today and came up with the following:

declare @obj sysname = 'schema.table'; select p.referencing_entity_name, c.* from sys.dm_sql_referencing_entities(@obj, 'object') as p cross apply sys.dm_sql_referenced_entities( concat(p.referencing_schema_name, '.', p.referencing_entity_name), 'object' ) as c where c.referenced_id = object_id(@obj) and referenced_minor_name in ('yourColumn');