sys.sql_expression_dependencies: The catalog view most SQL Server teams overlook

What is sys.sql_expression_dependencies?

sys.sql_expression_dependencies is a SQL Server catalog view that records every reference one SQL object makes to another. When a stored procedure calls a view, SQL Server records that relationship. When a view references a table, that's recorded too. When a function is called from a procedure that's called from a trigger — all of it is stored, automatically, from the moment the objects are created.

It ships with every edition of SQL Server from 2008 onward, and is available on Azure SQL Database, Azure SQL Managed Instance, Synapse Analytics, and Dataverse (Fabric). You've almost certainly had it in your environment for years without knowing it.

The view has the following key columns:

The query every DBA should run first

Start here. This query surfaces every object in your database ranked by how many other objects depend on it — your highest-risk objects, top to bottom:

SELECT
    referenced_entity_name      AS object_name,
    COUNT(*)                    AS caller_count
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NULL   -- same-database references only
GROUP BY referenced_entity_name
ORDER BY caller_count DESC;

The object at the top is your highest-dependency object — the one whose change or removal cascades through the most callers. On a mature production database it's common to see a single view or function with 40–80 callers. Most teams don't know which object that is until they change it and find out.

Finding everything that calls a specific object

Once you've identified a high-risk object, you want to know exactly what calls it:

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

This is the question that normally takes a developer 20 minutes of grepping through stored procedure definitions. sys.sql_expression_dependencies answers it in milliseconds.

Mapping the full call graph of one object

For deeper analysis, a recursive CTE lets you walk the entire dependency tree — everything that calls your object, and everything that calls those objects:

WITH dep_tree AS (
    -- Seed: direct callers of the target object
    SELECT
        referencing_entity_name     AS caller,
        referenced_entity_name      AS callee,
        1                           AS depth
    FROM sys.sql_expression_dependencies
    WHERE referenced_entity_name = 'YourObjectName'

    UNION ALL

    -- Recurse: everything that calls a caller
    SELECT
        d.referencing_entity_name,
        d.referenced_entity_name,
        dt.depth + 1
    FROM sys.sql_expression_dependencies d
    INNER JOIN dep_tree dt ON d.referenced_entity_name = dt.caller
    WHERE dt.depth < 10   -- guard against cycles
)
SELECT DISTINCT caller, callee, depth
FROM dep_tree
ORDER BY depth, caller;

This is the full upstream dependency tree — every object that would be affected if your target changed its signature, was renamed, or was dropped.

Cross-database dependencies

One of the most useful features of sys.sql_expression_dependencies is its support for cross-database references. When a stored procedure in database A queries a view in database B using three-part naming (DatabaseB.dbo.vwSomeView), that reference is recorded:

SELECT
    referencing_entity_name,
    referenced_database_name,
    referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
ORDER BY referenced_database_name, referenced_entity_name;

This query is particularly valuable before consolidating or retiring databases. It will surface every cross-database call that would break if a given database went away.

What it doesn't capture

Understanding the limits matters as much as understanding the capability:

• Dynamic SQL — references inside EXEC() or sp_executesql strings are not parsed and won't appear. If your codebase uses dynamic SQL heavily, sys.sql_expression_dependencies will undercount.
• OPENQUERY / linked servers — references through linked servers are not tracked.
• Application-layer calls — which application code calls which stored procedure is not captured here. This view tracks object-to-object dependencies within SQL Server only.
• Deferred name resolution — SQL Server resolves references at query execution time when it can't resolve them at parse time. These appear with is_caller_dependent = 1 and may have NULL for referenced_id.

Practical use cases

Before any schema change

Before renaming a column, altering a stored procedure signature, or adding a NOT NULL column to a table, run the callers query above. You'll know immediately which objects will break and need updating.

Migration planning

When migrating from SQL Server to Azure SQL, SQL Managed Instance, or PostgreSQL, the dependency map tells you the correct migration sequence. Objects with the most callers should move last — once all their dependents have been tested in the new environment. Objects with no callers can move in any order.

Identifying dead code

Objects that appear only as references but never as referencing objects may be leaf nodes — end-of-chain objects. Objects that appear neither as referencing nor as referenced are candidates for removal (though application-layer calls won't show up here, so verify before dropping).

Documentation

For databases with no existing documentation, sys.sql_expression_dependencies gives you a machine-readable call graph that can be turned into a dependency map, a data lineage diagram, or architecture documentation.

Comparing sys.sql_expression_dependencies with sys.dm_sql_referencing_entities

SQL Server also ships sys.dm_sql_referencing_entities, a table-valued function that returns the same caller information for a single named object. The difference is scope:

For most dependency analysis work, sys.sql_expression_dependencies is more powerful because it can be joined, filtered, grouped, and used in CTEs. Use sys.dm_sql_referencing_entities for quick one-off lookups in SSMS.

Putting it together: a pre-change checklist

Before touching any object in a production SQL Server database, a three-query check takes less than 30 seconds:

-- 1. How many objects call this one?
SELECT COUNT(*) AS direct_caller_count
FROM sys.sql_expression_dependencies
WHERE referenced_entity_name = 'YourObjectName';

-- 2. What are they?
SELECT referencing_entity_name,
       OBJECT_SCHEMA_NAME(referencing_id) AS schema_name
FROM sys.sql_expression_dependencies
WHERE referenced_entity_name = 'YourObjectName'
ORDER BY referencing_entity_name;

-- 3. Does anything else call those callers? (indirect blast radius)
SELECT DISTINCT d2.referencing_entity_name AS indirect_caller
FROM sys.sql_expression_dependencies d1
JOIN sys.sql_expression_dependencies d2
    ON d2.referenced_entity_name = d1.referencing_entity_name
WHERE d1.referenced_entity_name = 'YourObjectName';

If query 1 returns 0, the change is relatively low risk (subject to the dynamic SQL caveat above). If it returns 30, you have 30 objects to check. If query 3 surfaces another 60 indirect callers, you're looking at a much wider blast radius than you might have expected.

This is the information that prevents the Friday afternoon incident.

Summary

sys.sql_expression_dependencies has been available in SQL Server since 2008 and is present on every Azure SQL and Synapse database today. It records the full object call graph automatically — no setup required, no extra tooling. The queries above are the starting point for any dependency analysis, migration planning, or impact assessment work.

If you'd rather see the graph visually, Skupa reads this catalog live and renders it as an interactive dependency map — useful for the planning conversations where a list of object names is harder to reason about than a diagram.

← Back to blog