Have you ever encountered a situation where the sp_depends stored procedure in SQL Server does not provide accurate results when it comes to object dependencies? If so, you’re not alone. In this article, we will explore this issue and discuss alternative methods to accurately determine object dependencies in SQL Server.

Let’s start by creating two scenarios to demonstrate the problem. In Scenario 1, we will create objects first and then use them. In Scenario 2, we will create objects after they have been referenced.

Scenario 1: Normal Table Creation Order

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

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’s run three queries to determine the object dependencies in both 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

Upon examining the results, it becomes evident that sp_depends does not provide accurate results when the object creation order is different or when deferred name resolution is involved.

Instead, I recommend using the third method, which involves utilizing the sys.dm_sql_referencing_entities DMV. This method consistently provides correct object dependency information.

Here’s an example script that uses sys.dm_sql_referencing_entities to retrieve accurate object dependencies:

SELECT referencing_schema_name, referencing_entity_name,
       referencing_id, referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('YourObject', 'OBJECT');
GO

If you have your own script or method for finding object dependencies in SQL Server, I would love to hear about it. Feel free to share your script, and I will give you due credit in a future blog post.

Thank you for reading, and I hope this article helps you better understand SQL Server object dependencies.