9

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

11

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; 
  • 2
    I like the first method but I really don't like the second. syscomments and sysobjects are deprecated views present only for backward compatibility reasons; please stop using them and recommending them (especially to someone who's asking explicitly for a modern solution). – Aaron Bertrand Sep 27 '14 at 1:48
  • 1
    I have removed the second method. – Glen Swan Sep 27 '14 at 2:08
  • 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 '14 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 '14 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 '17 at 13:35

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.