SQL Server Object Dependencies Report: Find All References

calendar May 23, 2026 / · 8 min read · sql server dependencies object dependencies database administration refactoring sql scripts sys.sql_expression_dependencies sys.all_objects sys.all_columns impact analysis schema analysis sys.dm_sql_referenced_entities sys.dm_sql_referencing_entities sys.objects  ·
Share on: linkedin copy

Before renaming a column, retiring a view, or dropping a stored procedure, every DBA needs the answer to one question: what else depends on this object? This T-SQL script reads sys.sql_expression_dependencies and reports every referencing-and-referenced pair in the current database, with optional filtering to a single object name — the safe-refactoring impact analysis report.

Purpose and Overview

SQL Server tracks expression-level dependencies between user objects in the catalog view sys.sql_expression_dependencies. Every reference from a stored procedure, function, view, trigger, default, computed column, or check constraint to another object — table, view, function, procedure, type, or column — produces a row. The view supersedes the legacy sys.sql_dependencies view (deprecated in SQL Server 2008) and the older sp_depends stored procedure (which has always been unreliable for cross-database and late-bound references). Modern dependency tracking is metadata-only, schema-bound-aware, and accurate across cross-database name resolutions.

The script below joins sys.sql_expression_dependencies to sys.all_objects and sys.all_columns twice — once on the referencing side and once on the referenced side — to construct a human-readable report. Each row identifies the referencing object (and column if applicable) and the fully qualified referenced object name (server, database, schema, entity, column). Three additional flags — is_schema_bound_reference, is_ambiguous, and is_caller_dependent — surface the binding semantics behind each reference. An optional @ObjectName parameter scopes the result to a single object's dependencies in either direction, or runs the full-database scan when left as an empty string.

Code Breakdown

The complete script is shown below, followed by an explanation of each section. Set @ObjectName to a fully qualified object name (e.g., N'dbo.MyTable') to filter, or leave it as an empty string to get every dependency in the database.

 1/* Get object dependencies — see the References section for the source script */
 2
 3/* Provide an object name or leave empty to search the entire database */
 4DECLARE @ObjectName NVARCHAR(261) = N'';
 5
 6SELECT QUOTENAME(SCHEMA_NAME([ob].[schema_id]))
 7         + N'.' + QUOTENAME([ob].[name])
 8         + ISNULL(N'.' + QUOTENAME([col].[name]), N'')             AS [referencing_object_name],
 9       [ob].[type_desc]                                            AS [referencing_object_type],
10       ISNULL(QUOTENAME([sed].[referenced_server_name]) + N'.', N'')
11         + ISNULL(QUOTENAME([sed].[referenced_database_name]) + N'.', N'')
12         + ISNULL(QUOTENAME([sed].[referenced_schema_name]) + N'.', N'')
13         + ISNULL(QUOTENAME([sed].[referenced_entity_name]), N'')
14         + ISNULL(N'.' + QUOTENAME([tgcol].[name]), N'')           AS [fully_qualified_referenced_object],
15       ISNULL([tgob].[type_desc], [sed].[referenced_class_desc])   AS [referenced_object_type_or_class],
16       [sed].[referenced_server_name],
17       [sed].[referenced_database_name],
18       [sed].[referenced_schema_name],
19       [sed].[referenced_entity_name],
20       [tgcol].[name]                                              AS [referenced_column_name],
21       [sed].[is_schema_bound_reference],
22       [sed].[is_ambiguous],
23       [sed].[is_caller_dependent]
24FROM   sys.[sql_expression_dependencies] [sed]
25       INNER JOIN sys.[all_objects] AS [ob]
26               ON [sed].[referencing_id] = [ob].[object_id]
27       LEFT JOIN sys.[all_columns] [col]
28              ON [sed].[referencing_minor_id] = [col].[column_id]
29                 AND [sed].[referencing_id] = [col].[object_id]
30       LEFT JOIN sys.[all_objects] AS [tgob]
31              ON [sed].[referenced_id] = [tgob].[object_id]
32                 AND [sed].[referenced_server_name] IS NULL
33                 AND
34                 (
35                   [sed].[referenced_database_name] IS NULL
36                    OR [sed].[referenced_database_name] = DB_NAME()
37                 )
38       LEFT JOIN sys.[all_columns] [tgcol]
39              ON [sed].[referenced_minor_id] = [tgcol].[column_id]
40                 AND [sed].[referenced_id] = [tgcol].[object_id]
41                 AND [sed].[referenced_server_name] IS NULL
42                 AND
43                 (
44                   [sed].[referenced_database_name] IS NULL
45                    OR [sed].[referenced_database_name] = DB_NAME()
46                 )
47WHERE  [sed].[referencing_id] = CASE
48                                  WHEN @ObjectName <> N'' THEN OBJECT_ID(@ObjectName)
49                                  ELSE [sed].[referencing_id]
50                                END
51        OR [sed].[referenced_id] = CASE
52                                     WHEN @ObjectName <> N'' THEN OBJECT_ID(@ObjectName)
53                                     ELSE [sed].[referenced_id]
54                                   END
55ORDER  BY [referencing_object_name];

The Referencing Side: sys.all_objects + sys.all_columns

The first INNER JOIN resolves the referencing object: every row in sys.sql_expression_dependencies has a referencing_id (the object_id of the procedure, function, view, trigger, or constraint that contains the reference) and a referencing_minor_id (zero for object-level references, or a column ordinal for computed-column / check-constraint references). The script joins referencing_id to sys.all_objects.object_id and referencing_minor_id to sys.all_columns.column_id. sys.all_objects is used rather than sys.objects to include system objects in the result — useful when tracking down references from triggers or built-in views.

The QUOTENAME calls wrap the schema, object, and column names in square brackets. This matters when names contain reserved keywords, spaces, or special characters — the output is directly pasteable into other scripts without quoting issues.

The Referenced Side: Cross-Database-Safe Join

The two LEFT JOINs to sys.all_objects and sys.all_columns for the referenced object are filtered by two crucial predicates:

1AND [sed].[referenced_server_name] IS NULL
2AND ([sed].[referenced_database_name] IS NULL
3     OR [sed].[referenced_database_name] = DB_NAME())

These predicates guarantee that OBJECT_ID() and the catalog view joins only resolve names from the current database. SQL Server's dependency tracker happily records cross-database and cross-server references with three- and four-part names, but sys.all_objects only contains rows for objects in the current database. Without these predicates, a cross-database reference would silently match the wrong object_id (because IDs are not globally unique) and report misleading information.

For cross-database references, the script preserves the raw referenced_server_name, referenced_database_name, and referenced_schema_name columns so the operator can still see exactly what was referenced even when the target lives elsewhere.

Late Binding and the referenced_class_desc Fallback

The ISNULL([tgob].[type_desc], [sed].[referenced_class_desc]) expression matters because referenced_id can be NULL for late-bound references — places where the SQL Server engine recorded that a name was referenced but couldn't resolve it at parse time. Common examples include references to objects in a database that didn't exist when the procedure was created (deferred name resolution), references inside dynamic SQL, and references through synonyms that point at non-existent base objects. When referenced_id is NULL, referenced_class_desc ("OBJECT_OR_COLUMN", "TYPE", etc.) still tells you what kind of thing was referenced.

The CASE-in-WHERE Filter Pattern

The WHERE clause uses a CASE expression so the same script supports both modes — single-object lookup and full-database dump — without duplicated SQL:

1WHERE [sed].[referencing_id] = CASE
2                                 WHEN @ObjectName <> N'' THEN OBJECT_ID(@ObjectName)
3                                 ELSE [sed].[referencing_id]
4                               END
5   OR [sed].[referenced_id] = CASE
6                                WHEN @ObjectName <> N'' THEN OBJECT_ID(@ObjectName)
7                                ELSE [sed].[referenced_id]
8                              END

When @ObjectName is empty, the CASE returns the column to itself — equivalent to column = column, which matches every non-null row. When @ObjectName is set, the CASE returns the resolved OBJECT_ID and filters to dependencies in either direction (object referenced by, or referencing, the named object).

Key Benefits and Use Cases

  • Pre-refactor impact analysis: before renaming, retyping, or dropping any object, list every place it is referenced and every place it references — the canonical safe-refactoring report
  • Bidirectional: the OR-in-WHERE returns dependencies in both directions for a named object — what it uses and what uses it
  • Identifies cross-database and cross-server references by preserving referenced_server_name and referenced_database_name columns
  • Surfaces late-bound references that sp_depends misses entirely — the script returns rows even when referenced_id is NULL
  • Schema-bound flag (is_schema_bound_reference) tells you which references will block a base-object change vs. which will silently break at runtime
  • Quoted output is directly pasteable into ALTER scripts or rename scripts without name-escaping work

Performance Considerations

  • Metadata-only: the script reads catalog views (sys.sql_expression_dependencies, sys.all_objects, sys.all_columns) — no user data scanned. Safe to run during peak hours
  • Result-set size: a full-database run on a large schema can return tens of thousands of rows. Use the @ObjectName parameter to scope to one object when possible
  • Late-bound entries are noisy: each unresolved name produces a row with NULL referenced_id. Filter these out by adding AND [sed].[referenced_id] IS NOT NULL if you only want resolved dependencies
  • Cross-database references are not validated: sys.sql_expression_dependencies records the textual reference, not whether the target exists. The script preserves the name; verifying existence requires running the same query against the referenced database
  • Linked-server references appear with referenced_server_name populated and referenced_id NULL — they are not resolvable from the local catalog
  • Trigger-on-trigger and constraint-on-constraint chains are not single-hop: a multi-level "what depends on what depends on X" tree requires recursive logic (a CTE on top of this query), which the script intentionally does not include

Practical Tips

  • For a single-object dive, set @ObjectName to a three- or two-part name: N'dbo.MyTable' or N'sales.OrderHeader'. The script handles both directions of dependency automatically
  • Combine with sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities when you need late-binding-aware lookups in one direction only — they are the DMV equivalents of sp_depends
  • Save the output as a CSV before a major refactor and re-run it after the change to confirm no new dependencies appeared
  • For column-level dependencies, the referencing_minor_id / referenced_minor_id joins pull column names — useful for "who uses this column?" investigations where renaming or dropping a column is the goal
  • For schema-bound view audits, filter is_schema_bound_reference = 1. Those are the references that will produce an explicit error on the base object change rather than failing at execution time
  • Build a one-time pre-deploy report as part of any migration pipeline — every name change in the next release should be cross-checked against the current dependency graph

Conclusion

This object-dependencies query is the modern replacement for sp_depends and the legacy sys.sql_dependencies view. By joining sys.sql_expression_dependencies to sys.all_objects and sys.all_columns in a cross-database-safe way, it produces a clean impact-analysis report for any object in the database. Pair it with the DMVs sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities for late-binding scenarios, and run it as a pre-refactor gate against every release that renames a table, retypes a column, or drops a procedure.

References

Posts in this series