How to Find Database Dependencies in SQL Server Using
sql_expression_dependencies
In SQL Server, identifying dependencies between databases or database objects is essential for understanding the impact of changes and for troubleshooting. One of the key system catalog views for discovering such dependencies is sys.sql_expression_dependencies.
Basic Query to Find Database Object Dependencies
You can use the following query to list dependencies within the current database. This query returns the referencing database and object, the referenced schema and entity, the type of referenced object, and the referenced database name:
SELECT
DB_NAME() AS referencing_database_name,
OBJECT_NAME(referencing_id) AS referencing_entity_name,
ISNULL(referenced_schema_name, 'dbo') AS referenced_schema_name,
referenced_entity_name,
ao.type_desc AS referenced_entity_type,
ISNULL(referenced_database_name, DB_NAME()) AS referenced_database_name
FROM sys.sql_expression_dependencies sed
JOIN sys.all_objects ao ON sed.referenced_entity_name = ao.name;
Finding Nested Level Dependencies
To track nested or recursive dependencies — for example, to see all objects that depend on a specific object through multiple levels — you can use a recursive common table expression (CTE). Here’s an example that starts from a specific object named 'obj-1':
WITH NestDep AS (
SELECT
o.object_id AS referenced_id,
o.name AS referenced_name,
o.object_id AS referencing_id,
o.name AS referencing_name,
0 AS NestLvl
FROM sys.objects o
WHERE o.name = 'obj-1'
UNION ALL
SELECT
d1.referenced_id,
OBJECT_NAME(d1.referenced_id),
d1.referencing_id,
OBJECT_NAME(d1.referencing_id),
NestLvl + 1
FROM sys.sql_expression_dependencies d1
JOIN NestDep r ON d1.referenced_id = r.referencing_id
)
SELECT DISTINCT
referenced_id,
referenced_name,
referencing_id,
referencing_name,
NestLvl
FROM NestDep
WHERE NestLvl > 0
ORDER BY NestLvl, referencing_id;
This query helps you visualize dependency chains across multiple levels.
Detecting Linked Server Dependencies
If your SQL Server environment uses linked servers, dependencies may span across servers. To identify such cross-server dependencies, you can run this query that filters for objects referencing external servers:
SELECT
DB_NAME() AS referencing_database_name,
OBJECT_NAME(referencing_id) AS referencing_entity_name,
ISNULL(referenced_schema_name, 'dbo') AS referenced_schema_name,
referenced_entity_name,
ao.type_desc AS referenced_entity_type,
ISNULL(referenced_database_name, DB_NAME()) AS referenced_database_name,
referenced_server_name
FROM sys.sql_expression_dependencies sed
JOIN sys.all_objects ao ON sed.referenced_entity_name = ao.name
WHERE referenced_server_name IS NOT NULL;
Useful Reference
For more detailed information on sys.sql_expression_dependencies, visit the official Microsoft documentation:
