Different Ways to Find SQL Server Object Dependencies

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 andsys.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 column

Example 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:

Source collected from mssqltips.com