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 do give proper answers.
I have quite often seen that information_schema. routines give 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 afterwards.
USE TempDB GO CREATE TABLE dbo.TestTable ( ID INT, Name VARCHAR(100)) GO -- dbo.First is not created yet CREATE PROCEDURE dbo.Second AS EXEC dbo.First GO CREATE PROCEDURE dbo.First AS SELECT ID, Name FROM TestTable GO
Scenario 2: Objects are created afterwards and they are referenced first.
USE TempDB GO CREATE TABLE dbo.TestTable ( ID INT, Name VARCHAR(100)) GO CREATE PROCEDURE dbo.First AS SELECT ID, Name FROM TestTable GO -- dbo.First is already created CREATE PROCEDURE dbo.Second AS EXEC dbo.First GO
Now let us run following three queries on both the scenarios.
-- Method 1:Using sp_depends sp_depends 'dbo.First' GO -- Method 2:Using information_schema.routines SELECT * FROM information_schema.routines ISR WHERE CHARINDEX('dbo.First', ISR.ROUTINE_DEFINITION) > 0 GO -- Method 3:Using DMV sys.dm_sql_referencing_entities SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent FROM 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_dependent FROM 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.
Reference: Pinal Dave (http://blog.SQLAuthority.com)