THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tamarick Hill

SQL Dependency DMV’s

 

SQL Server Dynamic Management Views (DMV's), or more appropriately termed Dynamic Management Objects (DMO’s), have been around for quite a while now. They made their first appearance in SQL Server 2005 and they unlocked a wealth of information that was either impossible to get, or would take extremely complicated queries to get the information that you were looking for. Since I began learning about DMV's I have been a big advocate for using them. After the discovery of two new DMV's that were added in SQL Server 2008, my love for DMV's has grown even stronger.

These two DMV's in question are used to track down objects that are referencing a specific SQL Server object(sys.dm_sql_referencing_entities), or objects that are referenced by a specific SQL Server object(sys.dm_sql_referenced_entities).

DBA's are often tasked to identify all objects that may be depending upon a specific object or to find all objects that a specific object depends on. For example, let's use the AdventureWorks2012 database and let's assume that after deploying a new Employee Management solution, it is determined that we no longer need the table 'HumanResources. Department' which was used by our old legacy application. As a result, we need to identify all objects that may be referencing this table before we decide to drop it. To identify this we can use one of the SQL Dependency DMV's to get the needed information.

SELECT referencing_schema_name, referencing_entity_name

FROM sys.dm_sql_referencing_entities ('HumanResources.Department', 'OBJECT')

 

 

This simple DMV was able to identify all objects, in this case only 2 views, which are currently referencing our 'HumanResources.Department' table. After gathering this output, we realize that we can probably decommission the 'vEmployeeDepartment' view as well. Before we do so, we need to analyze all objects that this view references in its definition. Again a simple DMV query will return the information needed.

SELECT referenced_server_name, referenced_database_name, referenced_schema_name,

referenced_entity_name, referenced_minor_name

FROM sys.dm_sql_referenced_entities ('HumanResources.vEmployeeDepartment', 'OBJECT')

 

 

This DMV was able to give us a vast amount of information about the view. It was able to tell us every object that is referenced in the view all the way down to the column level. If this view had any Cross Database or Cross Server references, that information would also be displayed.

 

For more information about these two DMV's, please visit the below links:

http://msdn.microsoft.com/en-us/library/bb630351(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/bb677185(v=sql.110).aspx

 

 

Published Tuesday, May 07, 2013 4:57 PM by Tamarick Hill
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement