15

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
WHERE 
    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

1 Answer 1

14

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,
          c.column_id; 
5
  • 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.org/2008/09/09/…
    – tbone
    Commented 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
    Commented 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
    Commented 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
    Commented Oct 24, 2019 at 17:26
  • @10762409, thanks for that info. I see sys.dm_sql_referenced_entities may be the way to go, for both details lost in sys.sql_expression_dependencies, and for being future-proof. Commented Jul 10, 2023 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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