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
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 afterwords 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)

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

  1. 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.

    Like

  2. 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.

    Like

  3. 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.

    Like

  4. Pinal,

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

    Regards
    Mandan deo

    Like

  5. 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’

    Like

  6. 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

    Like

  7. 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.

    Like

  8. 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

    Like

  9. 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.

    Like

  10. 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

    Like

  11. 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

    Like

  12. 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

    Like

  13. Am trying to figure out a way to find the list of SPs which are using a table and a column. Let me know if someone has any inputs to do.

    Appreciate your help. – Thnx

    Like

      • @madhivanan, I guess you didn’t read the article: “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.”

        Like

  14. sys.dm_sql_referenced_entities always raise error when some referenced entities have not been found. then my question is:

    how do you ignore those errors, when your procedure contains “select into statement”?

    i have been looking around for days and yet no better solution than a try…catch. :-(

    any suggestion please?

    Like

  15. How about If you want to determine column dependencies, that is: which column(s) are used to calculate another for instance:

    create view vwTest as
    select t1.field1 + t2.field2 as Calculation from table1 t1 inner join table2 t2 on t1.id = t2.id

    Dependencie info should show something like:

    Object | Column | Depends On | Depends On Column
    ——–+—————+—————+——————-
    vwTest | Calculation | Table1 | Field1
    vwTest | Calculation | Table2 | Field2

    I’ve tried sql_expression_dependencies and other metatada views from sqlServer but I haven’t been able to get this info

    Because it doen’t link column with dependant columns

    Like

  16. Pingback: SQL SERVER – Weekly Series – Memory Lane – #015 « SQL Server Journey with SQL Authority

  17. Pinal,

    Under which conditions does your query fail to return any information?
    I am testing it with table names I know for sure either depend on other tables or have tables that depend on them. However, it always comes back with empty results. I am using SQL Server 2008.

    Thanks

    Like

  18. Dear Pinal and everyone, This is very interesting thread for me. Actually, yesterday itself I renamed one of my tables as the data was getting bulky and I decided not to drop the table or truncate the data. Just firstly I shall monitor the performance of the system after creating a new table by the same name, if every thing works fine then I shall drop the original table leaving the newly created table intact. So, I created a new table by the same name and renamed the original table. Now when I run the sp_depends the new table does not show any dependencies while the old one shows the dependencies whereas when I right click on the respective tables to “view dependencies” the situation is vice versa i.e the original table does not show dependencies and the newly created table shows them. Please advice…..

    Like

  19. I got the best results after using the query:SELECT object_name(object_id), * FROM sys.sql_modules WHERE definition LIKE ‘%%’ in sql server 2005.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s