SQL SERVER – Get the List of Object Dependencies – sp_depends and information_schema.routines and sys.dm_sql_referencing_entities

Recently, I read a question on my friend Ritesh Shah‘s SQL site regarding the following: sp_depends does not give appropriate results whereas information_schema.routines does give proper answer.

I have quite often seen that information_schema.routines gives proper dependency relationship where assp_depends returns an incorrect answer. However, as per book online sp_depends will be deprecated, and instead, sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities are recommended.

Let us quickly see where sp_depends fail and other solutions work fine.

Let us first create two scenarios.

Scenario 1: Normal Table Creation Order, where objects are created first and then used afterwords.

USE TempDB
GOCREATE TABLE dbo.TestTable( ID INT,Name VARCHAR(100))GO-- dbo.First is not created yetCREATE PROCEDURE dbo.SecondAS
EXEC dbo.First
GOCREATE PROCEDURE dbo.FirstAS
SELECT ID, NameFROM TestTable
GO

Scenario 2: Objects are created afterwords and they are referenced first.

USE TempDB
GOCREATE TABLE dbo.TestTable( ID INT,Name VARCHAR(100))GOCREATE PROCEDURE dbo.FirstAS
SELECT ID, NameFROM TestTable
GO-- dbo.First is already createdCREATE PROCEDURE dbo.SecondAS
EXEC dbo.First
GO

Now let us run following three queries on both the scenarios.

-- Method 1: Using sp_dependssp_depends 'dbo.First'GO-- Method 2: Using information_schema.routinesSELECT *FROM information_schema.routines ISRWHERE CHARINDEX('dbo.First', ISR.ROUTINE_DEFINITION) > 0
GO-- Method 3: Using DMV sys.dm_sql_referencing_entitiesSELECT referencing_schema_name, referencing_entity_name,referencing_id, referencing_class_desc, is_caller_dependentFROM sys.dm_sql_referencing_entities ('dbo.First', 'OBJECT');GO

Result from Scenario 1

Result from Scenario 2

It is clear that sp_depends does not give proper/correct results when the object creation order is different or following deferred name resolution.

I suggest the use of the third method, in which sys.dm_sql_referencing_entities is used.

Use the following script to get correct dependency:

SELECT referencing_schema_name, referencing_entity_name,referencing_id, referencing_class_desc, is_caller_dependentFROM sys.dm_sql_referencing_entities ('YourObject', 'OBJECT');GO

Let me know the type of scripts you use for finding Object Dependencies. I will post your script with due credit.