Transact SQL :- Get Object Dependency Using sys.sql_expression_dependencies

 

Background

As a database Administrator, one has to quickly look at SQL and try to identify which objects it touches.

API

There are a couple of pathways to identify objects that a programmable object references.

Inclusive are:

  1. sp_depends
  2. sys.sql_expression_dependencies

Code

Sample

Procedure

SP – [school].[usp_TripParticipants_List]

Here is a SP that list those going on a trip…..


use [DBLab]
go

if object_id('school.usp_TripParticipants_List') is null
begin

    exec('create procedure [school].[usp_TripParticipants_List] as ')

end
go

alter procedure [school].[usp_TripParticipants_List]
(
    @tripID bigint
)
as

begin

    set nocount on

    set XACT_ABORT on

    ; with cteSchoolBody
    as
    (
        select
                 tblS.id
               , tblS.identifier
               , tblS.firstname
               , tblS.lastname
               , [pool] = 'Student'

        from   [school].[student] tblS

        union all

        select
                 tblF.id
               , tblF.identifier
               , tblF.firstname
               , tblF.lastname
               , [pool] = 'Faculty'

        from   [school].[faculty] tblF

    )
    select
              [tripID] = tblT.[id]

            , [tripName] = tblT.[name]

            , cteSB.[pool]

            , cteSB.lastname

            , cteSB.firstname

            , cteSB.identifier

    from   [school].[trip] tblT

    --inner join [school].[tripParticipant] tblTP
    inner join [tripParticipant] tblTP

        on tblT.id = tblTP.tripID

    inner join cteSchoolBody cteSB
        on tblTP.participantID = cteSB.id

    where (

            (
                tblT.[id]
                    = isNull(@tripID, tblT.[id])
            )

          )

    order by
              [tripName]

            , [pool]

            , cteSB.lastname

            , cteSB.firstname

end
go

metadata

Procedure

SP – [master].[dbo].[sp_ObjectDependants]

Here is a SP that we use to seek out dependants….


use [master]
go

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

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

end
go

alter procedure [dbo].[sp_ObjectDependants]
(
    @object sysname
)
as

begin

    ;with cteObjectDependencies
    as
    (

        -- find dependency
        -- exec sp_help 'sys.sql_expression_dependencies'
        SELECT
            [referencingObjectID]
            = referencing_id

        , [referencingObject]
            =   quoteName
                (
                    OBJECT_SCHEMA_NAME ( referencing_id )
                )
                + '.'
                + quoteName
                    (
                        OBJECT_NAME(referencing_id)
                    )	

        , [referencingObjectType]
            = obj.[type_desc]

        , [referecedObjectClass]
            = sed.referenced_class_desc

        , [referencedObjectID]
            = sed.referenced_id

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

            , [referencedObjectType]
                = objRef.[type_desc]

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

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

        FROM sys.sql_expression_dependencies AS sed

        INNER JOIN sys.objects AS obj
            ON sed.referencing_id = obj.object_id

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

        WHERE sed.referencing_id = object_id(@object)

    )
    select

           [referencedObject]

         , [referencedObjectType]

         , [referecedObjectClass]

         , [isCallerDependent]

         , [isAmbiguous]

         , [referencedObjectID]

    from   cteObjectDependencies cteOD

    order by

           [referencedObject]

         , [referencedObjectType]

end
go

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

Demo

sp_depends

Sample


use [DBLab]
go

declare @object sysname

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

exec sp_depends @object

Output

sp_depends_20180615_1215PM

[dbo].[sp_ObjectDependants]

Sample


use [DBLab]
go

declare @object sysname

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

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

Output

objectDependency_20180615_1217PM

Source Code Control

GitHub

DanielAdeniji/TransactSQL.ObjectDependency
Link

Summary

sp_depends tells us the action invoked on dependent objects.

It lets us know whether updates were made or just reads.

Whereas sys.sql_expression_dependencies does not appear to make that distinction.

One thought on “Transact SQL :- Get Object Dependency Using sys.sql_expression_dependencies

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 )

Connecting to %s