Transact SQL :- Get Object Dependency Using sys.dm_sql_referenced_entities

 

Background

In a previous post we covered using sys.sql_expression_dependencies to track the objects utilized by our programmable object.

The post that uses sys.sql_expression_dependencies is here.

In this post we will discuss using sys.dm_sql_referenced_entities to accomplish same.

sys.dm_sql_referenced_entities

ResultSet

Column Description Version Active
is_selected The object or column is selected. SQL Server 2012 (11.x)
is_updated The object or column is modified ( Insert/Update/Merge). SQL Server 2012 (11.x)
is_select_all The object is used in a SELECT * clause (object-level only). SQL Server 2012 (11.x)
is_insert_all The object is used in an INSERT statement without a column list (object-level only). SQL Server 2016 (13.x) through SQL Server 2017.
is_incomplete The object or column has a binding error and is incomplete. SQL Server 2016 (13.x) SP2

Code

Stored Procedure

[dbo].[sp_ObjectReferencedEntities]

SP Code


use [master]
go

if object_id('[dbo].[sp_ObjectReferencedEntities]') is null
begin

    exec('create procedure [dbo].[sp_ObjectReferencedEntities] as ')

end
go

alter procedure [dbo].[sp_ObjectReferencedEntities]
(
      @object sysname
    , @referencingClass nvarchar(60) = 'object'
)
as

begin

    set nocount on;
    set XACT_ABORT on;

    declare @strLog nvarchar(600)

    if object_id(@object) is null
    begin

        set @strLog =
                        'In database '
                        + db_name()
                        + ', '
                        + @object
                        + ' not found!'
                        ;

        raiserror( @strLog, 16,1);

        return;

    end

    ;with cteObjectDependencies
    as
    (

        -- find dependency
        -- exec sp_help 'sys.dm_sql_referenced_entities'
        select

          [referecedObjectClass]
            = sre.referenced_class_desc

        , [referencedObjectID]
            = sre.referenced_id

        , [referencedObject]
            =
                isNull
                (
                    quoteName
                    (
                        sre.referenced_schema_name
                    )
                    + '.'
                , ''
                )
                + quoteName
                    (
                        sre.referenced_entity_name
                    )

            , [referencedObjectType]
                = objRef.[type_desc]

            , [isSelected]
                = case
                    when (sre.is_selected=0) then 'N'
                    else 'Y'
                  end	

            , [isInsert]
                = case
                    when (sre.is_insert_all=0) then 'N'
                    else 'Y'
                  end	

            , [isUpdated]
                = case
                    when (sre.is_updated=0) then 'N'
                    else 'Y'
                  end	

            , [isCallerDependent]
                = case
                    when (sre.is_caller_dependent=0) then 'N'
                    else 'Y'
                  end	

            , [isAmbiguous]
                = case
                    when (sre.is_ambiguous=0) then 'N'
                    else 'Y'
                  end	

        FROM sys.dm_sql_referenced_entities
            (
                  @object
                , @referencingClass
            ) AS sre   

        INNER JOIN sys.objects AS obj
            ON object_id(@object) = obj.object_id

        LEFT OUTER JOIN sys.objects AS objRef
            ON sre.referenced_id = objRef.object_id	

    )
    select
            distinct

           [referencedObject]

         , [referencedObjectType]

         , [referecedObjectClass]

         , [isSelected]

         , [isInsert]

         , [isUpdated]

         , [isCallerDependent]

         , [isAmbiguous]

         , [referencedObjectID]

    from   cteObjectDependencies cteOD

    order by

           [referencedObject]

         , [referencedObjectType]

end
go

exec sp_MS_marksystemobject '[dbo].[sp_ObjectReferencedEntities]'
go

Invoke


declare @object sysname

set @object = '[school].[usp_TripParticipants_List]'

--exec sp_depends @object

exec [dbo].[sp_ObjectReferencedEntities]
        @object = @object

Output

objectDependency_20180615_1217PM.png

Source Code Control

GitHub

DanielAdeniji/TransactSQL.ObjectDependency
Link

Summary

sys.dm_sql_referenced_entities offers more compared to sys.sql_expression_dependencies.

The former tracks usage and enjoys better support for identifying code that is a bit brittle.

 

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s