patternsqlModerate
Finding dependencies on a SPECIFIC COLUMN (modern way, without using sysdepends)
Viewed 0 times
withoutcolumnwayfindingsysdependsusingspecificmoderndependencies
Problem
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):
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.)
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_TypeSome 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.)
Solution
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;Code Snippets
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;Context
StackExchange Database Administrators Q#77813, answer score: 14
Revisions (0)
No revisions yet.