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

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

SQL SERVER - Get the List of Object Dependencies - sp_depends and information_schema.routines sp_depends1

Result from Scenario 2

SQL SERVER - Get the List of Object Dependencies - sp_depends and information_schema.routines sp_depends2

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 (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQLAuthority News – MVP Open Day South Asia – Jan 20, 2010 – Jan 23, 2010 – Review Part Fun
Next Post
SQL SERVER – Stream Aggregate Showplan Operator – Reason of Compute Scalar before Stream Aggregate

Related Posts

49 Comments. Leave new

  • Thanks for posting a blog on a common requirement of identifying the object dependencies in SQL Server. Just wanted to mention that the DMVs sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities were introduced in SQL Server 2008.

    Reply
  • Thanks for the post and just wanted to mention that the DMVs sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities were introduced in SQL Server 2008.

    Reply
  • Just two words “Thanks Champ”!!!!

    Reply
  • Pinal,
    Very helpful script, comes in very handy especially at times of redisigning/performance improvement type of situations.

    Reply
  • Heh. I implemented T/SQL parser in C#/.NET to track all the dependencies in the DB and produce XML file out of it some months ago. Unfortunately I don’t own the copyrights so I can’t make it public.

    Reply
  • Hi All,

    There is excellent post by Jacob Sebastian on this subject.

    His post describes how to find out the dependency chain of the object. I have used it personally and love it.

    Kind Regards,
    Pinal

    Reply
  • good stuff!

    Reply
  • Pinal,

    It is good for finding dependency check, saves time , Can you pl tell alternative way for sql 2005

    Regards
    Mandan deo

    Reply
  • Hi Pinal,

    Is there any way tracking object dependency across databases. Pls help.

    Regards,
    Adams

    Reply
  • Hi,
    I need to get the same result but in SQL 2005 and If I run above script in Sql 2005 it is giving me following error so please could you help me to sort out my problem.

    Erro is :
    Invalid object name ‘sys.dm_sql_referencing_entities’

    Reply
  • Naveen Gopinath
    October 1, 2010 12:07 pm

    For the above example

    IF i am using this table in a stored procedure

    this way

    .dbo.First

    DB…First
    this we will not get using sp_depends

    how to resolve this one??

    Regards
    Naveen Gopinath

    Reply
  • Naveen Gopinath
    October 1, 2010 12:09 pm

    In the above comment to make it clear

    Lets say my database name is TEST

    and my table name is First

    if i use in my sp

    TEST.dbo.First

    in some other sp i may be using Only First

    in some other sp i may be using Only dbo.First

    this information we will not get using sp_depends First

    How to get all the sps using the table.

    Reply
  • Well… none of them are giving PK, FK & Index information.

    Reply
  • Hi,

    How can we delete dependent stored procedures. We are cleaning our DB by deleting unwanted objects. But I’m getting dependency’s error. My DB is having around 600 Sp’s and 200 tables. 30% of them are backup copies. Previously we used to create backup of object while modifying that. After modification when we press F5 these backup object also comes under dependency’s list. So how do I delete them.

    Regards,
    Sanjay

    Reply
    • Run this to know the dependent objects.

      EXEC sp_depends ‘your table’

      Reply
      • Thanks for you Response madhivanan. I tried with sp_depends and with “select * from sys.dm_sql_referencing_entities (‘dbo.Object Name’, ‘OBJECT’)–for SQL Server 2008. But I have 100′s of objects. So I cannot do this for all. Is there any alternative for this.

      • Do you want to find relations for reach table individually?

  • Thanks for you Response madhivanan. I tried with sp_depends and with “select * from sys.dm_sql_referencing_entities (‘dbo.Object Name’, ‘OBJECT’)–for SQL Server 2008. But I have 100’s of objects. So I cannot do this for all. Is there any alternative for this.

    Reply
  • @Sanjay,
    You must have some prefix with the backup procedures/functions/views.. use it… :)

    Reply
  • I generally runs the following query to know the usage of sql object across the whole db:

    select b.name,b.xtype,a.text from syscomments a (nolock)
    inner join sysobjects b (nolock)
    on a.id = b.id
    where a.text like ‘%Table1%’

    in the example above ‘Table1’ is my table name.

    Thanks,
    Puneet Jaggi

    Reply
  • Hi Pinal, may be I’m late :) but I wrote an old query to solve the problem you set, I’ve tested in both scenarios and brings the right result.

    Here it is, I know is a bit old and kind of “artistic” -LOL- I wanted to analyze all kind of objects, but never implemented that part…..

    Regards
    Gabriel

    ====FOLLOW SCRIPT====

    /* ******************************************************
    * *
    * Retrieve depending objects on a given table *
    * *
    **************************************************** */

    DECLARE
    @ObjectName SYSNAME,
    @ObjectType VARCHAR(5),
    @ObjectID AS BIGINT

    /* Load table name to analyze */
    SET @ObjectName=’First’

    /* Press F5 */

    –SET @ObjectType=’TABLE’ <— Not Implemented

    SELECT TOP(1) @ObjectID = object_id
    FROM sys.objects
    WHERE name = @ObjectName
    –AND type = ISNULL(@ObjectType, type)

    PRINT @ObjectID

    SET NOCOUNT ON ;

    WITH DependentObjectCTE (DependentObjectID, DependentObjectName, ReferencedObjectName, ReferencedObjectID)
    AS
    (
    SELECT DISTINCT
    sd.object_id,
    OBJECT_NAME(sd.object_id),
    ReferencedObject = OBJECT_NAME(sd.referenced_major_id),
    ReferencedObjectID = sd.referenced_major_id
    FROM
    sys.sql_dependencies sd
    JOIN sys.objects so ON sd.referenced_major_id = so.object_id
    WHERE
    sd.referenced_major_id = @ObjectID
    UNION ALL
    SELECT
    sd.object_id,
    OBJECT_NAME(sd.object_id),
    OBJECT_NAME(referenced_major_id),
    object_id
    FROM
    sys.sql_dependencies sd
    JOIN DependentObjectCTE do ON sd.referenced_major_id = do.DependentObjectID
    WHERE
    sd.referenced_major_id sd.object_id
    )
    SELECT DISTINCT
    DependentObjectName
    FROM
    DependentObjectCTE c

    Reply
  • For quick and dirty results, I just use:
    SELECT TOP (100) PERCENT OBJECT_NAME(d.object_id) AS SP_Or_Function, OBJECT_NAME(d.referenced_major_id) AS TableReferenced
    FROM sys.sql_dependencies AS d INNER JOIN
    sys.all_sql_modules AS m ON m.object_id = d.object_id
    GROUP BY OBJECT_NAME(d.object_id), OBJECT_NAME(d.referenced_major_id)
    ORDER BY SP_Or_Function, TableReferenced

    Reply
  • Have fun online
    October 12, 2011 12:11 am

    That was a frankly amazing piece

    Reply

Leave a Reply

Menu