Technical Article

Dependencies on a specific table column

,

SQL Server provides a built-in stored procedure, sp_depends, that returns object dependencies.  Recently, I discovered a need for determining dependencies on a table column, not just the table itself.  For example, a table may be referenced by many stored procedures and views, but a specific column in that table may only be referenced in a few of those objects.  The following code will determine which views and stored procedures reference a specific column in a table, and whether the object is a view (V) or stored procedure (P).

SELECT OBJECT_NAME(sd.id) Dependent_Object,
(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 so.id = OBJECT_ID('table1')
AND sc.name = 'col1'

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating