Echoes from the field 7: Tracking object dependencies in SQL Server

Echoes from the field 7: Tracking object dependencies in SQL Server

This post describes how the object dependency tracking views provide more reliable insights into object dependencies than previous methods such as the use of the sp_depends system stored procedure.

During a recent consulting engagement, I was asked about the best way to determine which stored procedures and views made use of a particular table. In the past, the methods available from within SQL Server were not very reliable. Way back in SQL Server 2008, significant improvements were made in this area, yet I see so few people using them, at least not directly. Many will use them indirectly via SSMS.

In this post, let’s explore the problems with the previous mechanisms (that are still retained for backwards compatibility) and then see how the object dependency views improve the situation.

Did sp_depends really lie?

The system stored procedure that was originally provided for tracking the dependency of objects was sp_depends. There seems to be a general perception that it lies. While that assessment might be harsh, let’s start with an example of how it was used and then see why it has received a poor reputation. We’ll start by creating a table using the code below:

USE tempdb;
GO

CREATE TABLE dbo.Customers
( 
    CustomerID int IDENTITY(1,1) PRIMARY KEY,
    CustomerName varchar(50),
    IsRetailCustomer bit,
    OtherInfo varchar(200)
);
GO

Next, we’ll create a stored procedure and a view that are based on this table:

CREATE PROCEDURE dbo.GetCustomersByID
AS
    SELECT CustomerID, CustomerName, IsRetailCustomer, OtherInfo
    FROM dbo.Customers
    ORDER BY CustomerID;
GO

CREATE VIEW dbo.RetailCustomers
AS
    SELECT CustomerID, CustomerName, OtherInfo
    FROM dbo.Customers
    WHERE IsRetailCustomer <> 0;
GO

Now, we can investigate the values returned by the sp_depends system stored procedure. The procedure takes a single parameter for the object that we need to locate dependencies for. Here is an execution:

EXEC sp_depends N'dbo.Customers';
GO

When this procedure is executed, it returns the output shown below, which is exactly what we would hope to see, as it accurately reflects the dependencies:

The cause of difficulties is the concept of deferred name resolution in SQL Server. We can create procedures and views that are based on objects that do not exist. Here is some code that would not be permitted:

CREATE PROCEDURE dbo.ThisWillNotWork
AS
    SELECT CustomerID, CustomerName, NonExistentColumn 
    FROM dbo.Customers
    ORDER BY CustomerID;
GO

When we try to execute the code, the error shown in Figure 2 will be returned. This is because while the table exists, a column has been specified that is not part of the table.

What causes confusion though is that the code below is permitted, even though the Table dbo.SalesOrders does not exist:

CREATE PROCEDURE dbo.CustomerSalesOrdersByID
AS
    SELECT c.CustomerID, c.CustomerName,
           so.SalesOrderNumber, so.TotalValue
    FROM dbo.Customers AS c
    INNER JOIN dbo.SalesOrders AS so
    ON c.CustomerID = so.CustomerID
    ORDER BY c.CustomerID, so.SalesOrderNumber;
GO

Note that if we now re-execute the code from above, the same output that was shown earlier is returned. This is incorrect as the new stored procedure clearly has a dependency on the table dbo.Customers. Further, let us now create the missing table and re-execute sp_depends for both tables:

CREATE TABLE dbo.SalesOrders
( 
    SalesOrderNumber int PRIMARY KEY,
    CustomerID int REFERENCES dbo.Customers(CustomerID),
    TotalValue decimal(18,2) NOT NULL
);
GO

EXEC sp_depends N'dbo.Customers';
GO
EXEC sp_depends N'dbo.SalesOrders';
GO

When this code is executed, the dependencies for dbo.Customers still appear unchanged. What is even more interesting is that querying the dependencies for dbo.SalesOrders returns the output below:

This clearly is not true and this is why sp_depends has a reputation for lying. If we recreated all the stored procedures and views, the dependency information would suddenly be corrected.

Dependency Views

SQL Server has a set of expression dependency views. The most important view is the sys.sql_expression_dependencies view. We can query it via the code below:

SELECT OBJECT_NAME(referencing_id) AS ReferencingName, 
    o.type_desc AS ReferencingObjectType, 
referenced_schema_name + N'.' + referenced_entity_name 
  AS ReferencedObject
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o 
ON sed.referencing_id = o.object_id;
GO

When this code is executed, we receive the output shown:

Note that even though the objects were created out of sequence, this view returns the correct dependency information. Microsoft also supplied two dynamic management views (DMVs) that make it easier to query this view. One is focussed on the referenced object, the other is focussed on the referencing object. Here’s how to query these views:

SELECT referencing_schema_name 
           + N'.' 
           + referencing_entity_name AS ReferencingName
FROM sys.dm_sql_referencing_entities ('dbo.Customers', 'OBJECT');
GO

SELECT DISTINCT referenced_schema_name 
                    + N'.' 
                    + referenced_entity_name AS ReferencedName
FROM sys.dm_sql_referenced_entities('dbo.CustomerSalesOrdersByID', 'OBJECT')
GO

When this code is executed, we receive the output shown:

Partial Dependencies

An even stronger capability that these views provide is the support for tracking partial dependencies. Let’s create another view that references a non-existent table, and then query for partial dependencies:

CREATE PROCEDURE dbo.GetRetailCustomerSalesOrders
AS
SELECT c.CustomerID, c.CustomerName,
       so.SalesOrderNumber, so.TotalValue,
       sod.ProductID
FROM dbo.Customers AS c
INNER JOIN dbo.SalesOrders AS so
ON c.CustomerID = so.CustomerID
INNER JOIN dbo.SalesOrderDetails AS sod
ON so.SalesOrderNumber = sod.SalesOrderNumber
WHERE c.IsRetailCustomer <> 0
ORDER BY c.CustomerID;
GO

SELECT OBJECT_NAME(referencing_id) AS ReferencingName, 
    o.type_desc AS ReferencingObjectType, 
referenced_schema_name + N'.' + referenced_entity_name 
  AS ReferencedObject,
    referenced_id AS ReferencedID
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o 
ON sed.referencing_id = o.object_id;
GO

When this code is executed, we receive the output shown:

Note that a dependency is reported for dbo.SalesOrderDetails even though no such object exists. By querying for objects where the referenced_id column is NULL, we could use this query to locate objects that are referred to in code but do not exist. This will usually relate to an object that has not yet been created, or worse, to a typo in the code. Regardless, it is better to find this quickly rather than waiting for an error at run time.

Summary

In this post, we’ve seen why problems occurred with sp_depends and how the dependency views can avoid the problems and help us to produce higher quality code and to avoid the need to discover basic problems via unexpected errors at runtime.

2025-11-14