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 (https://blog.sqlauthority.com)
49 Comments. Leave new
How about using SYS.SQL_Expression_Dependencies?
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?
Thanks for this resource material, it is really educating and self teaching
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
bt it doesnt show the name of dependent table as well as column
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
It can happen if you are missing schema prefix. Try prefixing schema example dbo.Employee
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…..
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.
Thanks Lavanya. This works in every circumstance.
Is it possible to check sp_depends ‘columnname’ please any one guide me
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
I agree the scripts have been swapped.
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
I don’t think we can use that for dynamic queries.
I agree engine wouldn’t search in dynamic queries to find the object but there is any alternate way to do this?
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 ?
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).
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