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
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.
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.
Just two words “Thanks Champ”!!!!
Pinal,
Very helpful script, comes in very handy especially at times of redisigning/performance improvement type of situations.
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.
good stuff!
Pinal,
It is good for finding dependency check, saves time , Can you pl tell alternative way for sql 2005
Regards
Mandan deo
Hi Pinal,
Is there any way tracking object dependency across databases. Pls help.
Regards,
Adams
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’
Note that sys.dm_sql_referencing_entities is available from version 2008 onwards
try sp_depends
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
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.
Well… none of them are giving PK, FK & Index information.
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
Run this to know the dependent objects.
EXEC sp_depends ‘your table’
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.
@Sanjay,
You must have some prefix with the backup procedures/functions/views.. use it… :)
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
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
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
Love it – exactly what I was after.
Thanks heaps Lisa.
That was a frankly amazing piece
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
You can use sp_depends system stored procedure
EXEC sp_depends ‘yyour spname’
@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.”