Find Stored Procedures That Use a Table in SQL Server: Dependency Queries That Actually Work

Before you rename a column, change its type or drop a table, there’s a simple question to answer: which stored procedures, views, functions and triggers use this table? Get it wrong, and you find out only when a report or a nightly job fails in production.

SQL Server offers several ways to answer this question, and each has blind spots. This guide shows how to read dependency information correctly, how to complement it with a text search, and when no method can give you the full answer.

Method 1: Who Uses the Table? (sys.dm_sql_referencing_entities)

The most direct way to find every object that references a table is the sys.dm_sql_referencing_entities function. Pass the table name together with its schema:

SELECT re.referencing_schema_name,
       re.referencing_entity_name,
       o.type_desc
FROM sys.dm_sql_referencing_entities(N'dbo.Orders', N'OBJECT') AS re
JOIN sys.objects AS o ON o.object_id = re.referencing_id
ORDER BY o.type_desc, re.referencing_entity_name;

The result lists stored procedures, views, functions and triggers. This function only returns objects in the same database.

Method 2: The Dependency Catalog (sys.sql_expression_dependencies)

sys.sql_expression_dependencies holds every name-based dependency in a database, including references to other databases. To see the objects that use a table, together with references coming from another database:

SELECT OBJECT_SCHEMA_NAME(d.referencing_id) AS referencing_schema,
       OBJECT_NAME(d.referencing_id)        AS referencing_object,
       o.type_desc,
       d.referenced_database_name,
       d.referenced_schema_name,
       d.referenced_entity_name,
       d.is_ambiguous
FROM sys.sql_expression_dependencies AS d
JOIN sys.objects AS o ON o.object_id = d.referencing_id
WHERE d.referenced_entity_name = N'Orders'
ORDER BY referencing_object;

Run this query in the other databases too, to catch objects that use the table from elsewhere; rows with referenced_database_name filled in are cross-database references. To loop over all databases, you can use the loop patterns in our cursor guide.

Column Level: Which Object Uses Which Column?

Before changing a column, the real question is who uses that column. sys.dm_sql_referenced_entities returns the tables and columns an object references. Find the objects that use the table, then check each one at column level:

DECLARE @Table sysname = N'Orders', @Column sysname = N'CustomerNo';

SELECT DISTINCT
       OBJECT_SCHEMA_NAME(d.referencing_id) + N'.' + OBJECT_NAME(d.referencing_id) AS referencing_object,
       re.referenced_minor_name AS column_name
FROM sys.sql_expression_dependencies AS d
CROSS APPLY sys.dm_sql_referenced_entities(
        OBJECT_SCHEMA_NAME(d.referencing_id) + N'.' + OBJECT_NAME(d.referencing_id), N'OBJECT') AS re
WHERE d.referenced_entity_name = @Table
  AND re.referenced_entity_name = @Table
  AND re.referenced_minor_name = @Column;

An object that has become invalid (for example, a procedure referencing a column that no longer exists) can make this query raise an error. Such an error already points to a broken object you should fix before the change.

Method 3: Text Search (sys.sql_modules)

Dependency catalogs can’t see dynamic SQL. Queries run with EXEC('SELECT ... FROM Orders') or sp_executesql only appear in the text, so you can only find them by looking at the source code:

SELECT OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
       OBJECT_NAME(m.object_id)        AS object_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 N'%Orders%'
ORDER BY o.type_desc, object_name;

A text search casts a wide net: it also catches similar names such as OrdersArchive and comments. Compare the results with the dependency query; objects that appear only in the text search are usually either using dynamic SQL or false positives. For names containing LIKE wildcards such as the underscore, escape the character with square brackets.

Why Dependency Information Can Be Incomplete

• Deferred name resolution: a procedure can be created before the table it references exists. The dependency record may then be missing or ambiguous (is_ambiguous).
• References without a schema: references written without dbo. resolve against the caller’s default schema and can stay ambiguous.
• Dynamic SQL: is never recorded as a dependency.
• Synonyms: the dependency is recorded on the synonym; check the table the synonym points to separately.

Pre-Change Checklist

1. Objects using the table are listed with sys.dm_sql_referencing_entities.
2. For column changes, column-level dependencies are checked.
3. A text search in sys.sql_modules covers dynamic SQL.
4. Cross-database references from other databases are checked.
5. Usage in applications, ETL and reporting is confirmed with the teams involved.
6. The change is tried in a test environment first, recompiling every affected object.