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.

SQL Server – Identify Objects with dependencies outside of current database

Background

Needed a quick way to identify referenced objects that are not contained in my current database either to having being dropped, located in a different database, or located on a linked server.

 

DevioBlog

DevioBlog has a good concise query.

A write-up is available here.

And, here it is in it’s entirety.


select o.name, d.referenced_entity_name, *
from sys.sql_expression_dependencies  d

inner join sys.objects o 
       on d.referencing_id = o.object_id

where referenced_id is null


 

Code

 


/*

    sys.sql_expression_dependencies (Transact-SQL)
    v2008
    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-expression-dependencies-transact-sql?view=sql-server-2017

    ID of the referenced entity. 


    a) The value of this column is never NULL for schema-bound references. 

    b) The value of this column is always NULL for cross-server and cross-database references.

    c) NULL for references within the database if the ID cannot be determined. 

        Objects dropped

    
    d) For non-schema-bound references, the ID cannot be resolved in the following cases:

        The referenced entity does not exist in the database.

*/
select 
          
        [object]
        = quoteName
            (
                isNull
                (
                    tblSS.[name]
                    , ''
                )
            )

            + '.'

            + quoteName
            (
                isNull
                (
                    tblSO.name
                    , ''
                )
            )

        , [objectType]
            = tblSO.[type_desc]

        , [referencedDatabase]
            = tblSED.referenced_database_name

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

        , [classofReferencedObject]
            = tblSED.referenced_class_desc

from sys.objects tblSO

inner join sys.schemas tblSS

        on tblSO.schema_id = tblSS.schema_id

inner join sys.sql_expression_dependencies  tblSED

        on tblSO.object_id = tblSED.referencing_id

where (

        (
            ( tblSED.[referenced_id] is null )
        )

    )

Summary

Thank goodness information about referenced objects are cataloged in the sys.sql_expression_dependencies system table.

If the referenced object is not in the contextual database, the referenced_id is logged as null.

Also as part of clean-up effort the referenced_id column is nulled out whenever an object is dropped.