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

July, the 31 Days of SQL Server DMO’s – Day 16 (sys.dm_sql_referenced_entities)

The sys.dm_sql_referenced_entities Dynamic Management Function returns a result set of all objects that are being referenced as part of the definition of the object that you reference within the function argument section. You can pass in either a Server trigger, database trigger, or any object  such as a table or view. To better illustrate how this function works lets look at a stored procedure and a view in the AdventureWorks2012 database.

If we script out the uspGetEmployeeManagers stored procedure we will see the code in the screenshot below.

image

If we needed to know all of the tables, columns, views, functions, etc. that are referenced in this stored procedure, we could easily just read through the code since its not too long.  But what if the code was 1000 lines worth of complex code? It would be difficult to gather a list of all dependent objects without running the risk of missing something. This is where the sys.dm_sql_referenced_entities function comes in handy. If we run a quick query against this function, we can immediately see the benefit:

SELECT referenced_schema_name, referenced_entity_name, referenced_minor_name,
       referenced_class_desc, is_ambiguous, is_selected, is_updated, is_select_all
FROM sys.dm_sql_referenced_entities ('dbo.uspGetEmployeeManagers', 'OBJECT')

image

As we see from the result set, this function returned all of the objects that are referenced in the stored procedure. From the first 3 columns that I am selecting from this function, we can see the Schema name, Object name, and column name fields. The next column is the referenced class description which can either be an OBJECT OR COLUMN, TYPE, XML SCHEMA COLLECTION, or a PARTITION FUNCTION. The next column, is_ambiguous, describes whether or not the referenced object is ambiguous and can resolve to multiple different objects during run-time. The is_updated column describes whether or not this object is updated as part of the stored procedure. Lastly, the is_select_all column refers to whether or not a particular column is included as part of a select * statement.

This DMV is very helpful when you need to identify all objects that are being referenced as part of another object.

For more information about this DMV, see the below Books Online link:

http://msdn.microsoft.com/en-us/library/bb677185.aspx

Follow me on Twitter @PrimeTimeDBA

Published Tuesday, July 16, 2013 9:14 AM by Tamarick Hill

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