How to find every stored procedure that references a SQL Server table

The direct query

SQL Server records every dependency one object creates on another in sys.sql_expression_dependencies. To find stored procedures that reference a specific table:

SELECT
    d.referencing_entity_name   AS procedure_name,
    OBJECT_SCHEMA_NAME(d.referencing_id) AS schema_name
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o ON o.object_id = d.referencing_id
WHERE d.referenced_entity_name = 'Orders'
  AND d.referenced_schema_name = 'dbo'
  AND o.type = 'P'              -- P = stored procedure
ORDER BY procedure_name;

This returns every stored procedure whose definition names the table. SQL Server maintains this graph automatically — it's updated every time an object is created or altered.

Extend to all object types

In most cases you want the full picture — not just stored procedures, but views, functions, and triggers that also reference the table, since those are often called by the procedures you're about to change:

SELECT
    d.referencing_entity_name,
    OBJECT_SCHEMA_NAME(d.referencing_id) AS schema_name,
    o.type_desc
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o ON o.object_id = d.referencing_id
WHERE d.referenced_entity_name = 'Orders'
  AND (d.referenced_schema_name = 'dbo' OR d.referenced_schema_name IS NULL)
ORDER BY o.type_desc, d.referencing_entity_name;

SQL Server object type codes for reference: P = stored procedure, V = view, FN = scalar function, IF = inline table-valued function, TF = multi-statement table-valued function, TR = trigger.

Rank tables by how many procedures reference them

Before a migration or schema freeze, it's useful to know which tables are referenced by the most code — those are the highest-risk objects to touch:

SELECT
    d.referenced_entity_name    AS table_name,
    COUNT(*)                    AS referencing_object_count
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o ON o.object_id = d.referencing_id
WHERE d.referenced_database_name IS NULL     -- same database only
  AND o.type IN ('P', 'V', 'FN', 'IF', 'TF', 'TR')
GROUP BY d.referenced_entity_name
ORDER BY referencing_object_count DESC;

The top of this list is where your migration risk lives. A table referenced by 80 procedures is one you change last — after every one of its dependents has been validated in the target environment.

Gap 1: Dynamic SQL

sys.sql_expression_dependencies only captures references that SQL Server can resolve at parse time. A stored procedure that builds and executes a query string at runtime — common in reporting databases and older data warehouses — is not recorded in the catalog:

-- This reference won't appear in sys.sql_expression_dependencies
CREATE PROCEDURE dbo.GetOrders @month INT AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) =
        N'SELECT * FROM dbo.Orders WHERE MONTH(OrderDate) = ' + CAST(@month AS NVARCHAR);
    EXEC(@sql);
END

To catch dynamic SQL references, search the module definition text directly:

SELECT o.name, o.type_desc
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE m.definition LIKE '%Orders%'
  AND o.type IN ('P', 'V', 'FN', 'IF', 'TF', 'TR')
ORDER BY o.type_desc, o.name;

This returns everything whose definition text contains the table name — including dynamic SQL strings. Scan the results manually; you'll also get false positives from comments and coincidental name matches.

Gap 2: Cross-database references

A stored procedure in DatabaseA that queries DatabaseB.dbo.Orders using three-part naming won't appear in DatabaseB's sys.sql_expression_dependencies — the reference is recorded in DatabaseA's catalog, not the target database's. To find cross-database callers, you'd need to run the referencing query across every database on the instance, or use a server-wide view if available.

When assessing a table for migration, always note: "no results in this query" means no in-database callers with static SQL. It doesn't mean nothing depends on the table from outside.

The combined checklist

For a complete pre-change or pre-migration assessment:

1. Run the sys.sql_expression_dependencies query above to get all static callers
2. Run the sys.sql_modules text search to catch dynamic SQL and confirm coverage
3. Check sys.foreign_keys for tables that point FK constraints at this one — those have a data-integrity dependency even if no code references the table directly
4. Check application-layer calling patterns — no catalog query can see ETL jobs, Power BI datasets, or application code that calls the table directly

Steps 1 and 2 together take under two minutes and catch the vast majority of SQL-layer dependencies.

← Back to blog · Full table dependency checklist →