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:
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"):
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):
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:
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:
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:
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:
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:
Note that the last column is a comma separated list of the dependent objects:
Source collected from mssqltips.com
No comments :
Post a Comment