By: Svetlana Golovko | Updated: 2013-07-16 | Comments (25) | Related: 1 | 2 | 3 | 4 | 5 | More > Comparison Data and Objects
Problem
There are a lot of resources available about system objects that will display object dependencies. There are also great examples of how you can use it. In this tip we will share a couple of useful scripts that you can use for your application development or database upgrades.
Solution
In this tip, there are several methods to list object dependencies that were used in previous versions of SQL Server.
Another great tip explains how to use the latest dynamic management views (sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities) and catalog view (sys.sql_expression_dependencies).
Our tip will provide useful examples that could be used by Developers as well as by Database Administrators. This could also be a good exercise to dig into your databases and learn/document different types of dependencies.
Example 1: Cross-database dependencies
Our developer inherited an old application and asked for help to identify cross-database dependencies. There were many integration points, but they were not documented anywhere.
Here is the query that helps to find objects referenced by other databases:
SELECT OBJECT_NAME (referencing_id) AS referencing_object, referenced_database_name,
referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_database_name IS NOT NULL
AND is_ambiguous = 0; Note: This may also include other three-part name references if the is_ambiguous filter is omitted. See Books Online (BOL) for more information about this column and its meaning.
A similar query could be used to find objects referencing linked servers (BOL:"cross-server dependencies that are made by specifying a valid four-part name"):
SELECT OBJECT_NAME (referencing_id) AS referencing_object, referenced_server_name,
referenced_database_name, referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referenced_server_name IS NOT NULL
AND is_ambiguous = 0;Example 2: Find specific column dependencies
In this example, the developer noticed a typographical error in the old database code and needs to rename the column. But before the renaming, he needs to find out where else this column might be used (if there are any dependent views and stored procedures):
SELECT OBJECT_NAME (referencing_id),referenced_database_name,
referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE OBJECT_NAME(d.referenced_id) = 'Customers' -- table that has miss-spelled column
AND OBJECT_DEFINITION (referencing_id) LIKE '%Cstomer%'; -- miss-spelled columnExample 3: Find schema-bound dependencies
The next query will show schema-bound dependencies which include views created with the "SCHEMABINDING" keyword, computed columns and check constraints:
SELECT OBJECT_NAME(d.referencing_id) AS referencing_name, o.type_desc referencing_object_type, d.referencing_minor_id AS referencing_column_id, d.referenced_entity_name, d.referenced_minor_id AS referenced_column_id, cc.name as referenced_column_name FROM sys.sql_expression_dependencies d JOIN sys.all_columns cc ON d.referenced_minor_id = cc.column_id AND d.referenced_id = cc.[object_id] JOIN sys.objects o ON d.referencing_id = o.[object_id] WHERE d.is_schema_bound_reference = 1 -- AND d.referencing_minor_id > 0
Add filter "AND d.referencing_minor_id > 0" to find only computed column dependencies.
Example 4: Display nest level
With this example we can get results similar to SQL Server Management Studio (SSMS) for the object's dependencies:
WITH DepTree (referenced_id, referenced_name, referencing_id, referencing_name, NestLevel)
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 NestLevel
FROM sys.objects o
WHERE o.name = 't_demo_4'
UNION ALL
SELECT d1.referenced_id,
OBJECT_NAME( d1.referenced_id) ,
d1.referencing_id,
OBJECT_NAME( d1.referencing_id) ,
NestLevel + 1
FROM sys.sql_expression_dependencies d1
JOIN DepTree r ON d1.referenced_id = r.referencing_id
)
SELECT DISTINCT referenced_id, referenced_name, referencing_id, referencing_name, NestLevel
FROM DepTree WHERE NestLevel > 0
ORDER BY NestLevel, referencing_id;
The results will look similar to this output:
Example 5: Finding Nested Views with more than 4 levels
Nested views may affect performance in a bad way, especially if they were created without looking at the underlying code and if they were referenced just because "it returned data I needed". Read more in this article: What Are Your Nested Views Doing?.
As per Microsoft's recommendations: "(Views) Nesting may not exceed 32 levels. The actual limit on nesting of views may be less depending on the complexity of the view and the available memory".
It is not always the case that nested views will decrease database performance, but you may want to find them and probably verify that they perform well. Based on the modified query above, we have this code that will return nested views with more than 4 levels:
WITH DepTree
AS
(
SELECT o.name, o.[object_id] AS referenced_id ,
o.name AS referenced_name,
o.[object_id] AS referencing_id,
o.name AS referencing_name,
0 AS NestLevel
FROM sys.objects o
WHERE o.is_ms_shipped = 0 AND o.type = 'V'
UNION ALL
SELECT r.name, d1.referenced_id,
OBJECT_NAME( d1.referenced_id) ,
d1.referencing_id,
OBJECT_NAME( d1.referencing_id) ,
NestLevel + 1
FROM sys.sql_expression_dependencies d1
JOIN DepTree r
ON d1.referenced_id = r.referencing_id
)
SELECT DISTINCT name as ViewName, MAX(NestLevel) AS MaxNestLevel
FROM DepTree
GROUP BY name
HAVING MAX(NestLevel) > 4
ORDER BY MAX(NestLevel) DESC; Example 6: Finding dependencies for the objects using specific data types
As you may know, TEXT, NTEXT and IMAGE data types are deprecated and may not be supported in future versions of SQL Server. If you plan to upgrade your application and replace deprecated data types this query could be a good start. It will show all objects that use these data types and show object dependencies:
WITH DepTree
AS
(
SELECT DISTINCT o.name,
o.[object_id] AS referenced_id ,
o.name AS referenced_name,
o.[object_id] AS referencing_id,
o.name AS referencing_name,
0 AS NestLevel
FROM sys.objects o JOIN sys.columns c
ON o.[object_id] = c.[object_id]
WHERE o.is_ms_shipped = 0
AND c.system_type_id IN (34, 99, 35) -- TEXT, NTEXT and IMAGE
UNION ALL
SELECT r.name,
d1.referenced_id,
OBJECT_NAME(d1.referenced_id) ,
d1.referencing_id,
OBJECT_NAME( d1.referencing_id) ,
NestLevel + 1
FROM sys.sql_expression_dependencies d1
JOIN DepTree r
ON d1.referenced_id = r.referencing_id
)
SELECT name AS parent_object_name,
referenced_id,
referenced_name,
referencing_id,
referencing_name,
NestLevel
FROM DepTree t1 WHERE NestLevel > 0
ORDER BY name, NestLevel Note: This will return all dependent objects for the objects that use the data types above (even if the dependent objects do not reference columns with these data types). So, you will have to review the code individually using the script in example 1.
Example 7: Complete dependencies report
The query below returns one record for each database's object with dependencies:
SELECT DB_NAME() AS dbname,
o.type_desc AS referenced_object_type,
d1.referenced_entity_name,
d1.referenced_id,
STUFF( (SELECT ', ' + OBJECT_NAME(d2.referencing_id)
FROM sys.sql_expression_dependencies d2
WHERE d2.referenced_id = d1.referenced_id
ORDER BY OBJECT_NAME(d2.referencing_id)
FOR XML PATH('')), 1, 1, '') AS dependent_objects_list
FROM sys.sql_expression_dependencies d1 JOIN sys.objects o
ON d1.referenced_id = o.[object_id]
GROUP BY o.type_desc, d1.referenced_id, d1.referenced_entity_name
ORDER BY o.type_desc, d1.referenced_entity_name
Note that the last column is a comma separated list of the dependent objects:
Note: Please run all these queries in your Development or Test environment before running them in Production.
Next Steps
- Find nested views that might affect your database performance
- Document existing dependencies
- Make your applications compatible with the latest SQL Server version where possible by replacing deprecated features
- Learn more about Dynamic Management Views and Functions and about Catalog Views
- Read more tips about Data Types
About the author
Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2013-07-16
