I need to find all views & stored procedures that consume not just a certain table, but a specific column in a table.

The following "seems" to work, but there are numerous warnings to be careful with this method (not reliable for a variety of reasons, soon to be deprecated, etc):

SELECT object_name(so.id) TableName, sc.name ColumnName, OBJECT_NAME(sd.id) DependentObjectName,
(SELECT xtype FROM sysobjects so WHERE so.id = sd.id) Object_Type
FROM sysobjects so INNER JOIN syscolumns sc
ON so.id = sc.id
INNER JOIN sysdepends sd
ON so.id = sd.depid and sc.colid = sd.depnumber
    object_name(so.id) = 'MyTableName'
AND sc.name = 'MyColumnName'
order by object_name(so.id), Object_Type

Some alternative approaches that are often referenced are sys.sql_dependencies and sys.sql_expression_dependencies, but neither of these have column level granularity.

Does anyone know a way of doing this? (Or even if you know definitively that it literally cannot be done, it would be helpful to know.)


1 Answer 1


Here is an AdventureWorks example on viewing column dependencies.

SELECT ReferencingObject = SCHEMA_NAME(o1.schema_id) + '.' + o1.name,
       ReferencedObject = SCHEMA_NAME(o2.schema_id) + '.'
                          + ed.referenced_entity_name,
       ColumnName = c.name,
       ReferencedObjectType = o2.type,
       ReferencingObjecType = o1.type
FROM   AdventureWorks2012.sys.sql_expression_dependencies ed
       INNER JOIN AdventureWorks2012.sys.objects o1
               ON ed.referencing_id = o1.object_id
       INNER JOIN AdventureWorks2012.sys.objects o2
               ON ed.referenced_id = o2.object_id
       INNER JOIN AdventureWorks2012.sys.sql_dependencies d
               ON ed.referencing_id = d.object_id
                  AND d.referenced_major_id = ed.referenced_id
       INNER JOIN sys.columns c
               ON c.object_id = ed.referenced_id
                  AND d.referenced_minor_id = c.column_id
WHERE  SCHEMA_NAME(o1.schema_id) + '.' + o1.name = 'HumanResources.vEmployee' AND c.name = 'JobTitle'
ORDER  BY ReferencedObject,
  • This looks excellent. Can you comment on whether this AT ALL or POSSIBLY suffers from the same "stale, out of date" issues as sysdepends? See: sqlblog.com/blogs/aaron_bertrand/archive/2008/09/09/…
    – tbone
    Sep 29, 2014 at 15:57
  • ALSO: Any idea if this would resolve nested dependencies? ie: a PROD depends on a VIEW which depends on TABLE.COLUMN: if we set criteria on TABLE & COLUMN, will both the view and the proc show in the results?
    – tbone
    Sep 29, 2014 at 16:03
  • I just don't understand what purpose is served by where condition. TS wanted to find dependecies on the specific column. I think the condition should constraint c.name and ed.referenced_id/ed.referenced_entity_name, shouldn't it?
    – pkuderov
    Sep 29, 2017 at 13:35
  • Here in 2019 - sys.sql_dependencies is now in maintenance mode, and the Microsoft team is recommending using only sys.sql_expression_dependencies. That being said - it doesn't look like sys.sql_expression_dependencies captures the same level of detail.
    – 10762409
    Oct 24, 2019 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.