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

John_27109 0
Viewed 106494

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.

sql-server-2008 sql-server
9 Answers

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

user_168129650

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');
user_5682090
Related