Find Dependent Objects

One new feature that SQL Server 2008 offers is more reliable dependency information. SQL Server 2005 offered a DMV (dynamic management view) called sys.sql_dependencies. It is now replaced by a more reliable sys.sql_expression_dependencies.

The following script will show all the procedures that reference a given table name, along with the columns the procedure references also. Please note, this does not take into account any dynamic SQL.

USE MYDatabase
GO

DECLARE @TableName VARCHAR(100)
SET @TableName = 'mytable'

SELECT
 SourceSchema                  = OBJECT_SCHEMA_NAME(sed.referencing_id)
 ,SourceObject                 = OBJECT_NAME(sed.referencing_id)
 ,ReferencedDB                 = ISNULL(sre.referenced_database_name, DB_NAME())
 ,ReferencedSchema             = ISNULL(sre.referenced_schema_name,
OBJECT_SCHEMA_NAME(sed.referencing_id))
 ,ReferencedObject             = sre.referenced_entity_name
 ,ReferencedColumnID   = sre.referenced_minor_id
 ,ReferencedColumn             = sre.referenced_minor_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id)
+ '.' + OBJECT_NAME(sed.referencing_id), 'OBJECT') sre
WHERE sed.referenced_entity_name = @TableName
AND sre.referenced_entity_name = @TableName

16 comments
mbourgon 23 Mar 2012 at 1:08 pm

Clever alternative to the way I was doing it, which was using a recursive CTE. Thanks!

John Galt 18 Sep 2011 at 8:12 am

Hello Derek,

Thank you for your offering. However, this query throws an un-captured error when no columns are detected. I received this when I applied it to one of my databases. It needs more seasoning.

Msg 2020, Level 16, State 1, Line 5
The dependencies reported for entity “dbo.rpt_Ca____” do not include references to columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.

Derek Dieter 19 Sep 2011 at 10:03 pm

Hi John,

Yes, I believe it is a bug. If you look, I bet that procedure references an object that does not exist. Instead of SQL Server bypassing it, it raises an error.

Rathnam 09 Aug 2011 at 10:04 pm

Hi,

if i am using the above script i am getting the below error.

Msg 2020, Level 16, State 1, Line 7
The dependencies reported for entity “dbo.xxxx” do not include references to columns. This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity. Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.

Thanks & Regards,
Rathnam

Derek Dieter 10 Aug 2011 at 6:40 am

Hi Rathnam,

I believe that is a SQL 2008 bug. I’m not sure but think it may be fixed in R2. The only workaround is likely to drop the object.

Best of Luck,
Derek

Rathnam 11 Aug 2011 at 3:21 am

Hi Derek,

I am using below version

Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (Intel X86) Apr 2 2010 15:53:02 Copyright (c) Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Thanks & Regards,
Rathnam

Derek Dieter 12 Aug 2011 at 10:11 pm

Hi Rathnam,

After looking into this error, it seems as if SQL 2008 does not allow procedures that have orphaned references to procedures, tables, or columns that do not exist. It seems as if this procedure would be able to overlook this issue, however they do not. In my opinion, it would be a great enhancement to overlook this issue.

Thanks,
Derek

SQL Server 2005 vs SQL Server 2008 25 Jun 2009 at 8:45 am

[...] New DMV shows all object dependencies [...]

Featured Articles

 Site Author