sp_depends is deprecated — what to use instead in SQL Server 2022 and Azure SQL

What sp_depends did

sp_depends was a system stored procedure that returned two result sets for a given object: the objects it depended on, and the objects that depended on it. A typical call:

EXEC sp_depends 'dbo.OrderSummary';

The problem was the data source it read from. sp_depends relied on sysdepends — a legacy system table that predates late-binding name resolution in SQL Server. Once SQL Server introduced late binding (the ability to create objects that reference names that don't exist yet, resolved at execution time), sysdepends could silently miss references or return stale entries. The metadata was structurally unreliable, and no amount of patching fixed the underlying model.

sys.sql_expression_dependencies, introduced in SQL Server 2008, replaced sysdepends with a properly maintained dependency graph. sp_depends was formally deprecated in SQL Server 2012. In SQL Server 2022, it was removed. Running it on SQL Server 2022 or Azure SQL Database returns:

Could not find stored procedure 'sp_depends'.

The replacement: sys.sql_expression_dependencies

sys.sql_expression_dependencies is a catalog view that records every reference one SQL object makes to another — stored procedures, views, functions, triggers — automatically, from the moment the objects are created or altered.

To find everything that calls a specific object (the most common use of sp_depends):

SELECT
    referencing_entity_name,
    OBJECT_SCHEMA_NAME(referencing_id) AS referencing_schema
FROM sys.sql_expression_dependencies
WHERE referenced_entity_name = 'OrderSummary'
ORDER BY referencing_entity_name;

To find everything a specific object calls (its own dependencies):

SELECT
    referenced_entity_name,
    referenced_schema_name,
    referenced_database_name   -- populated for cross-database references
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('dbo.OrderSummary')
ORDER BY referenced_entity_name;

Unlike sp_depends, this view handles late binding correctly, exposes cross-database references via referenced_database_name, and can be filtered, joined, and used in CTEs for recursive analysis.

For single-object lookups: the referencing entity DMFs

SQL Server also ships two table-valued dynamic management functions that answer the same questions for a single named object:

-- Everything that calls OrderSummary
SELECT referencing_schema_name, referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.OrderSummary', 'OBJECT');

-- Everything OrderSummary calls
SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name
FROM sys.dm_sql_referenced_entities('dbo.OrderSummary', 'OBJECT');

sys.dm_sql_referenced_entities also returns column-level binding information via referenced_minor_name, which sys.sql_expression_dependencies doesn't expose in the same way. This makes it useful before renaming a column — you can see exactly which columns a given object references.

The limitation: the DMFs can't be self-joined for recursive traversal, and sys.dm_sql_referencing_entities throws an error if any object in the dependency chain no longer compiles. On a database with broken views or stale references, it may error before returning results.

Comparison

What none of them capture

All three sources share the same fundamental blind spot: references assembled at runtime. A stored procedure that builds a query string with EXEC() or sp_executesql — common in older data warehouses — is invisible to the dependency catalog, the DMFs, and the now-removed sp_depends. On codebases with heavy dynamic SQL, complement any catalog analysis with a text search of sys.sql_modules:

SELECT o.name, o.type_desc, m.definition
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE m.definition LIKE '%YourTableName%'
ORDER BY o.type_desc, o.name;

This catches dynamic SQL strings, computed column definitions, and any reference the catalog view couldn't resolve at parse time. Scan results manually for actual dependencies rather than coincidental name matches in comments.

← Back to blog · Deep dive: sys.sql_expression_dependencies →