Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Different Ways to Find SQL Server Object Dependencies


By:   |   Read Comments (20)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Comparison Data and Objects

Attend a SQL Server Conference for FREE >> click to learn more


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 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 this example we can get results similar to the 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:

Show nest level

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:

The last column has comma separated 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


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Sunday, February 19, 2017 - 7:45:21 PM - Svetlana Golovko Back To Top

Thanks for your comments, olomhuszar.

 

I am glad you found workaround for the infinit loop. I wasn't able to reproduce the issue, but I may not have the exact scenario to test.

 

Nest level 4 is used just as an example. You can use any number to check the level you need (1 or 2 or 3 etc.)


Monday, February 06, 2017 - 6:44:57 AM - olomhuszar Back To Top

 

Why 4 nest level?

The cte get infinit because different objects references the same object. To avoid this just concatanate the already referenced entities and filter it in the reqcursive member.


Tuesday, January 10, 2017 - 11:29:11 PM - Svetlana Golovko Back To Top

 

 Hi Doug,

 

Yes, you can do this. 

You can query sys.synonyms view (base_object_name column) and get the synonyms refering to another database(s). You will need to have a cursor or something like this to loop through the databases. Then you will need to use query from example #5 above in dynamic SQL.

I will post a couple of examples as soon as I have a chance.

 

Thanks,

Svetlana


Friday, December 30, 2016 - 11:36:57 AM - Douglas Osborne Back To Top

 I was wondering how this could be altered to account for synonyms? The process shows me a depth of 5 when it is a view of another table in a different DB via a synonym?

Thanks,

Doug

 


Tuesday, July 23, 2013 - 10:06:15 AM - Svetlana Golovko Back To Top

Thank you for reading, Geri Reshef, and for your feedback


Tuesday, July 23, 2013 - 4:06:01 AM - Geri Reshef Back To Top

Thank you for this usefull tip.


Friday, July 19, 2013 - 8:45:07 AM - Svetlana Golovko Back To Top

Thank you, Kalaivendan!


Friday, July 19, 2013 - 7:07:10 AM - Kalaivendan Back To Top

Thank you so much.. Very good article.


Thursday, July 18, 2013 - 5:28:59 PM - Svetlana Golovko Back To Top

Thank you, Kon and Sudhev!


Thursday, July 18, 2013 - 12:26:24 AM - Sudhev Back To Top

 

Nice to have information.. thanks a lot Svetlana....


Wednesday, July 17, 2013 - 9:13:44 PM - Kon Back To Top

Nice Nice Nice... Nice Nice.. Congrates Congrates ... Congrates.. Nice Congrates..


Wednesday, July 17, 2013 - 4:52:19 PM - Svetlana Golovko Back To Top

Thank you, SqlNightOwl. This is a very good point. I usually ask my developers to change this kind of conditions as well. I need to start reviewing DBA scripts as well, not only developers :)


Tuesday, July 16, 2013 - 7:48:58 PM - SqlNightOwl Back To Top

Great article!  My only "do different" would be to use numeric comparisons

OLD:  OBJECT_NAME(d.referenced_id) = 'Customers'

NEW:  d.referenced_id = OBJECT_ID(N'Customers')

 

... but, still great article.  It met my main criteria of learning something.


Tuesday, July 16, 2013 - 7:42:12 PM - Svetlana Golovko Back To Top

Thanks, Prasad!


Tuesday, July 16, 2013 - 4:45:45 PM - Prasad Back To Top

Nice one!! Excellent job!!


Tuesday, July 16, 2013 - 10:14:53 AM - Svetlana Golovko Back To Top

Thank you, everybody. I am glad you found it useful.


Tuesday, July 16, 2013 - 9:35:07 AM - Junior Galv„o - MVP Back To Top

,

Congratulations por article.

Great content.

Regards.


Tuesday, July 16, 2013 - 9:22:56 AM - Ranga Back To Top

Good one, thanks for sharing!!!


Tuesday, July 16, 2013 - 9:20:54 AM - Jeremy Kadlec Back To Top

Svetlana,

Great tip today and congrats on your 10th tip!

Thank you for all that you do for the community!

Thank you,
Jeremy Kadlec
Community Co-Leader


Tuesday, July 16, 2013 - 2:22:15 AM - Nilesh Argade Back To Top

Excellent knowledge sharing. Thanks.


Learn more about SQL Server tools