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

  • Pavan Nayakanti
    October 12, 2011 1:28 pm

    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

    Reply
    • You can use sp_depends system stored procedure

      EXEC sp_depends ‘yyour spname’

      Reply
      • @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.”

  • How about using SYS.SQL_Expression_Dependencies?

    Reply
  • 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?

    Reply
  • Thanks for this resource material, it is really educating and self teaching

    Reply
  • 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

    Reply
  • bt it doesnt show the name of dependent table as well as column

    Reply
  • 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

    Reply
  • avaneesh1@gmail.com
    April 10, 2013 12:17 pm

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

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

    Reply
  • Is it possible to check sp_depends ‘columnname’ please any one guide me

    Reply
  • Does the Scenario 1 and 2 got interchanged?

    In Scenario 1 where its mentioned that Normal Table Creation Order, where objects are created first and then used afterwords.,

    The procedure second is created referring the procedure First which is not yet created.

    Am i missing something?

    Thanks
    Mini

    Reply
  • Hi Pinal,

    How can we find dependent objects if that object is used in dynamic stored procedure?

    In Scenario, I used a table object in dynamic written stored procedure but when I have checked that table object using sp_depends it returns message as “Object does not reference any object, and no objects reference it.” which itself result is invalid. It should show stored procedure name where it has been used.

    Please advise!!

    Thanks

    Reply
  • I agree engine wouldn’t search in dynamic queries to find the object but there is any alternate way to do this?

    Reply
  • Savio Fernandoavio
    February 24, 2017 1:25 am

    Is there a way we could list the dependencies for all the tables in a database , and on another level , list the dependencies for all the objects across all the databases ?

    Reply
  • I am facing problem to find out the columns in the insert query inside a SP like,
    INSERT INTO tablename(column1,column2)
    SELECT @column1,@column2 FROM #TempTable

    I use the following query to find dependencies on a stored procedure

    SELECT referenced_entity_name,* FROM
    sys.dm_sql_referenced_entities (‘dbo.WEB_ANALYSER’, ‘OBJECT’)

    but i am not able to get the dependencies(column1,column2) which is mentioned in the insert query(mentioned above).

    Reply
  • Hi Pinal,

    I am facing problem to find out the columns in the insert query inside a SP like,
    INSERT INTO tablename(column1,column2)
    SELECT @column1,@column2 FROM #TempTable

    I use the following query to find dependencies on a stored procedure

    SELECT referenced_entity_name,* FROM
    sys.dm_sql_referenced_entities (‘dbo.WEB_ANALYSER’, ‘OBJECT’)

    but i am not able to get the dependencies(column1,column2) which is mentioned in the insert query(mentioned above).

    Please reply to this ASAP

    Reply

Leave a Reply

Menu