Find all references to a table column in SQL Server 2008

Question

Is there a way to find all references to a table column in a SQL Server 2008 database? I need to know which other tables or stored procedures or views the column is referenced from.

score 4 · Accepted Answer · answered Mar 08 '13 at 22:24

If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

enter image description here

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??

score 3 · Answer 2 · answered Jan 20 '15 at 07:23

When I need to analyze impact due to table schema change, I use following steps for analysis. This may not be complete but helpful!

Sp_help [TableName] : This helps me to find all FOREIGN KEY constraints References. I can also find any other tables referencing this table as FOREIGN Key.
Sp_depends [TableName]: This helps to find Procedures, Functions, and Views using this table.
Brute force method: I use different System tables and functions to check for specific keyword in SQL Jobs, modules, etc

FYI: Sp_help [TableName] Sp_depends [TableName]

select top 10 * from msdb.dbo.sysjobsteps where command like '%%'

SELECT top 10 Name, OBJECT_DEFINITION(OBJECT_ID)
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%%' 

SELECT top 10 OBJECT_NAME(object_id), *
FROM sys.sql_modules
WHERE definition LIKE '%%'

sp_xxx are generally deprecated in favor of sys.xxx functions. In this case sys.dm_sql_referencing_entities(). Still sp_depends pointed me in the right direction so +1. — Gerald Davis, Jun 15 '15 at 14:52
A similar sql statement that I think is more complete(triggers?) is at: http://stackoverflow.com/a/30432937/1991614 not sure if it finds foriegn keys tho — bitcoder, Nov 11 '16 at 19:16

score 2 · Answer 3 · answered Mar 18 '13 at 17:07

You can try using a tool such as ApexSQL Search. It searches for object names but it also searches for a list of dependent objects even if columns in dependent tables/views are named differently.

Other solution is to use system views and/or system functions to get the data you need. Suggestion is to use sys.foreign_keys, sys.objects, sys.all_columns to get the data you need.

score 1 · Answer 4 · answered Aug 16 '19 at 17:07

I have spent a good amount of time trying to find a way to identify column level dependencies in a quick way without having to search text or use third party applications. The other challenge is finding dependencies across multiple databases where table names may repeat, which will cause false positives when searching SP text.

As of SQL 2008, there is a function that returns dependencies across databases on a field level.

The code below works with a few exceptions:

It will fail if there are stored procedures with invalid references on tables/fields that have been deleted (Incidently I found this to be useful to find SPs that had been accidentally broken by table modifications).
It doesn't find all dependencies in cases where the SP is using temp tables in unusual ways.
In some cases I found that it was returning false positives for complex stored procedures.

MSDN Documentation

This code should be run from within the database where the SP is in order to be able to cross to other database dependencies.

SELECT      
--SP, View, or Function
ReferencingName = o.name,
ReferencingType = o.type_desc,

--Referenced Field
ref.referenced_database_name, --will be null if the DB is not explicitly called out
ref.referenced_schema_name, --will be null or blank if the DB is not explicitly called out
ref.referenced_entity_name,
ref.referenced_minor_name,

--this will tell you the reference type
ref.is_selected,
ref.is_updated

FROM sys.objects AS o 
cross apply sys.dm_sql_referenced_entities('dbo.' + o.name, 'Object') ref
where o.type in ('FN','IF','V','P','TF')

Find all references to a table column in SQL Server 2008

4 Answers4