Transact SQL – Which objects are referenced?

 

Background

In our last post we discussed removing all other objects other than a few designated tables that a problematic queries are using.

 

Identify objects in use

 

Outline

In this post we will discuss a couple of options available for identifying the tables a programmable object uses.

 

Options

sp_depends

sp_depends is the classic Stored Procedure for doing so.

Syntax


exec sp_depends @objname = @object

 

Sample


declare @object sysname

set @object = '[dbo].[usp_Agent_Retrieve]'

exec sp_depends @objname = @object

 

Output

sp_depends_20171212_0547PM

 

sys.dm_sql_referenced_entities

sys.dm_sql_referenced_entities is a later iteration of sp_depends.

Syntax


select *

FROM sys.dm_sql_referenced_entities (@object, 'OBJECT') tblRef

 

Sample


declare @object sysname

set @object = '[dbo].[usp_Agent_Retrieve]'

SELECT 
          [referenced_schema_name]
        , [referenced_entity_name]
        , [columnCount] = count(*)
        , [isSelected]
            = max(cast(tblRef.[is_selected] as tinyint))
        , [isUpdated]
            = max(cast(tblRef.[is_updated] as tinyint))
/*
        , [isInsert]
            = max(cast(tblRef.[is_insert_all] as tinyint))
*/

FROM sys.dm_sql_referenced_entities (@object, 'OBJECT') tblRef

group by

          tblRef.[referenced_schema_name]
        , tblRef.[referenced_entity_name]

order by
          tblRef.[referenced_schema_name]
        , tblRef.[referenced_entity_name]

 

 

Output

sysDOTdm_sql_referenced_entities__20171212__0558PM

 

 

Summary

There are some distinct advantages to choosing the more modern path, sys.dm_sql_referenced_entities.

Inclusive are:

  1. Because it is table value function compared to Stored Procedure ( SP ), it is a bit easier to consume
    • We can issue distinct and group by and just get the object names and not have to take entities and columns
  2. It provides detailed information regarding use-cases
    • It tells us which columns are involved in updates, selects, and so forth

 

References

  1. Microsoft
    • Docs / SQL / Relational databases / System dynamic management views
      • sys.dm_sql_referenced_entities
        • sys.dm_sql_referenced_entities (Transact-SQL)
          Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s