Phil Factor's It_Depends(...), with enhancements

IF OBJECT_ID (N'dbo.It_Depends') IS NOT NULL
  DROP FUNCTION dbo.It_Depends
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.It_Depends (
     @ObjectName              NVARCHAR(200)
    ,@ObjectsOnWhichItDepends BIT
)

RETURNS @References TABLE (
     ThePath           NVARCHAR(MAX) --the ancestor objects delimited by a N'/'
    ,TheFullEntityName NVARCHAR(200)
    ,TheType           NVARCHAR(20)
    ,iteration         INT
)

/*
   Summary: Phil Factor's It_Depends(...) function

            This Table function returns a a table giving the dependencies of
            the object whose name is supplied as a parameter.

            At the moment, only objects are allowed as a parameter, You can
            specify whether you want those objects that rely on the object,
            or those on whom the object relies.

   Constraints:

     *

   Note(s):

     * Compatibility: SQL Server 2005+

     * Blog post overview and comments:
       https://www.red-gate.com/simple-talk/sql/t-sql-programming/dependencies-and-references-in-sql-server/

     * Adapted from source code published at:
       https://www.red-gate.com/simple-talk/wp-content/uploads/imported/2284-ItDepends.html

     * TODO: Must add assemblies, must allow entities such as types to be
       specified

     * Returns (in this example output, we have set @ObjectName=ObjectTableName
       and @ObjectsOnWhichItDepends=0, to retrieve objects dependent on
       ObjectTableName):

       THEPATH                                            THEFULLENTITYNAME              THETYPE ITERATION
       -------------------------------------------------- -----------------------------  ------- ---------
       dbo.ObjectTableName                                dbo.ObjectTableName            U       1
       dbo.ObjectTableName/dbo.ReferencingStoredProcedure dbo.ReferencingStoredProcedure P       2
       ...

     * Example usage:

            /*
             * AdventureWorks.Employee: objects that depend on it
             * Note: function SPACE(...) indents rows to indicate hierarchical
             * relationships
             */

            USE AdventureWorks
            SELECT      [Result] =
                        SPACE(F.iteration * 4) +
                        F.TheFullEntityName +
                        N' (' + RTRIM(F.TheType) + N')'

              FROM      dbo.It_Depends(N'Employee', 0) F

            ORDER BY    F.ThePath

            /*
             * AdventureWorks.Employee: objects that it depends on
             * Note: function SPACE(...) indents rows to indicate hierarchical
             * relationships
             */

            USE AdventureWorks
            SELECT      [Result] =
                        SPACE(F.iteration * 4) +
                        F.TheFullEntityName +
                        N' (' + RTRIM(F.TheType) + N')'

              FROM      dbo.It_Depends(N'Employee', 1) F

            ORDER BY    F.ThePath

   Date(s): 2015-09-01 PF  Initial revision
            2015-09-20 PF  Allowed both types of dependencies, returned full
                           detail table
            2016-03-28 JB  Add the schema name consistently to column
                           "@DatabaseDependencies.TheReferredEntity" by adding
                           an INNER JOIN to sys.schemas and modifying the
                           SELECT clause
            2016-10-25 KS  Insert the current dependency object type
                           (DatabaseDependencies.EntityType) instead of the
                           parent dependency object type
                           (previousReferences.TheType), in the INSERT INTO
                           SELECT clause
            2018-11-25 KLS Column sys.sql_expression_dependencies.referenced_id
                           is NULLable, so substitute with lookup
                           OBJECT_ID(sys.sql_expression_dependencies.referenced_entity_name)
                           when inserting soft dependencies into table
                           variable DatabaseDepencies. Sequences EntityType
                           changed from SYN to SEQ. Qualified object names
                           and used aliases consistently. Converted to return
                           Unicode N*CHAR types.
            2018-11-27 KLS SQL inline table-valued function ('IF') type added
                           as acceptable soft dependency
*/

AS

BEGIN

DECLARE     @DatabaseDependencies TABLE ( EntityName        NVARCHAR(200)
                                         ,EntityType        NCHAR(5)
                                         ,DependencyType    NCHAR(4)
                                         ,TheReferredEntity NVARCHAR(200)
                                         ,TheReferredType   NCHAR(5)
                                        )

/*
 * Populate @DatabaseDependencies via multiple UNION ALL statements, one for
 * each hard and soft dependency type. Table of hard references:
 * https://www.red-gate.com/simple-talk/wp-content/uploads/imported/2284-Dependencies.png
 */

INSERT INTO @DatabaseDependencies ( EntityName
                                   ,EntityType
                                   ,DependencyType
                                   ,TheReferredEntity
                                   ,TheReferredType
                                  )

-- Tables that reference UDTs

SELECT       [EntityName] =
             OBJECT_SCHEMA_NAME(O.object_id) + N'.' + O.name
            ,[EntityType]        = O.type
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] = TY.name
            ,[TheReferredType]   = N'UDT'

  FROM      sys.objects AS O

            INNER JOIN sys.columns AS C
            ON C.object_id = O.object_id

            INNER JOIN sys.types AS TY
            ON TY.user_type_id = C.user_type_id

 WHERE      TY.is_user_defined = 1

UNION ALL

-- UDTTs that reference UDTs

SELECT       [EntityName] =
             OBJECT_SCHEMA_NAME(TT.type_table_object_id) + N'.' + TT.name
            ,[EntityType]       = N'UDTT'
            ,[DependencyType]   = N'hard'
            ,[TheReferredEntity]= TY.name
            ,[TheReferredType]  = N'UDT'

  FROM      sys.table_types AS TT

            INNER JOIN sys.columns AS C
            ON C.object_id = TT.type_table_object_id

            INNER JOIN sys.types AS TY
            ON TY.user_type_id = C.user_type_id

 WHERE      TY.is_user_defined = 1

UNION ALL

-- Tables/views that reference triggers

SELECT       [EntityName] =
             OBJECT_SCHEMA_NAME(OBJECT_CHILD.object_id) + N'.' + OBJECT_CHILD.name
            ,[EntityType]        = OBJECT_CHILD.type
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] =
             OBJECT_SCHEMA_NAME(OBJECT_PARENT.object_id) + N'.' + OBJECT_PARENT.name
            ,[TheReferredType]   = OBJECT_PARENT.type

  FROM      sys.objects AS OBJECT_PARENT

            INNER JOIN sys.objects AS OBJECT_CHILD
            ON OBJECT_CHILD.parent_object_id = OBJECT_PARENT.object_id

 WHERE      OBJECT_CHILD.type = N'TR'

UNION ALL

-- Tables that reference defaults via columns (only default objects)

SELECT       [EntityName] =
             OBJECT_SCHEMA_NAME(OBJECT_COLUMNS.object_id) + N'.' + OBJECT_NAME(OBJECT_COLUMNS.object_id)
            ,[EntityType]        = N'U'
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] =
             OBJECT_SCHEMA_NAME(O.object_id) + N'.' + O.name
            ,[TheReferredType]   = O.type

  FROM      sys.objects AS O

            INNER JOIN sys.columns AS OBJECT_COLUMNS
            ON OBJECT_COLUMNS.default_object_id = O.object_id

 WHERE      O.parent_object_id = 0

UNION ALL

-- Types that reference defaults (only default objects)

SELECT       [EntityName]        = TYPES.name
            ,[EntityType]        = N'UDT'
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] =
             OBJECT_SCHEMA_NAME(O.object_id) + N'.' + O.name
            ,[TheReferredType]   = O.type

  FROM      sys.objects AS O

            INNER JOIN sys.types AS TYPES
            ON TYPES.default_object_id = O.object_id

 WHERE      O.parent_object_id = 0

UNION ALL

-- Tables that reference rules via columns

SELECT       [EntityName] =
             OBJECT_SCHEMA_NAME(OBJECT_COLUMNS.object_id) + N'.' + OBJECT_NAME(OBJECT_COLUMNS.object_id)
            ,[EntityType]        = N'U'
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] =
             OBJECT_SCHEMA_NAME(O.object_id) + N'.' + O.name
            ,[TheReferredType]   = O.type

  FROM      sys.objects AS O

            INNER JOIN sys.columns AS OBJECT_COLUMNS
            ON OBJECT_COLUMNS.rule_object_id = O.object_id

UNION ALL

-- Types that reference rules

SELECT       [EntityName]        = TYPES.name
            ,[EntityType]        = N'UDT'
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] =
             OBJECT_SCHEMA_NAME(O.object_id) + N'.' + O.name
            ,[TheReferredType]   = O.type

  FROM      sys.objects AS O

            INNER JOIN sys.types AS TYPES
            ON TYPES.rule_object_id = O.object_id

UNION ALL

-- Tables that reference XmlSchemaCollections

SELECT       [EntityName] =
             OBJECT_SCHEMA_NAME(OBJECT_COLUMNS.object_id) + N'.' + OBJECT_NAME(OBJECT_COLUMNS.object_id)
            ,[EntityType]        = N'U'
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] = X.name
            ,[TheReferredType]   = N'XMLC'

  FROM      sys.columns AS OBJECT_COLUMNS --should we eliminate views?

            INNER JOIN sys.xml_schema_collections AS X
            ON X.xml_collection_id = OBJECT_COLUMNS.xml_collection_id

UNION ALL

-- Table types that reference XmlSchemaCollections

SELECT       [EntityName] =
             OBJECT_SCHEMA_NAME(OBJECT_COLUMNS.object_id) + N'.' + OBJECT_NAME(OBJECT_COLUMNS.object_id)
            ,[EntityType]        = N'UDTT'
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] = X.name
            ,[TheReferredType]   = N'XMLC'

  FROM      sys.columns AS OBJECT_COLUMNS

            INNER JOIN sys.table_types AS TT
            ON TT.type_table_object_id = OBJECT_COLUMNS.object_id

            INNER JOIN sys.xml_schema_collections AS X
            ON X.xml_collection_id = OBJECT_COLUMNS.xml_collection_id

UNION ALL

-- Procedures that reference XmlSchemaCollections

SELECT       [EntityName] =
             OBJECT_SCHEMA_NAME(PARAMS.object_id) + N'.' + O.name
            ,[EntityType]        = O.type
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] = X.name
            ,[TheReferredType]   = N'XMLC'

  FROM      sys.parameters AS PARAMS

            INNER JOIN sys.xml_schema_collections AS X
            ON X.xml_collection_id = PARAMS.xml_collection_id

            INNER JOIN sys.objects AS O
            ON O.object_id = PARAMS.object_id

UNION ALL

-- Table references table

SELECT       [EntityName] =
             OBJECT_SCHEMA_NAME(TBL.object_id) + N'.' + TBL.name
            ,[EntityType]        = TBL.type
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] =
             OBJECT_SCHEMA_NAME(referenced_object_id) + N'.' + OBJECT_NAME(referenced_object_id)
            ,[TheReferredType]   = N'U'

  FROM      sys.foreign_keys AS FK

            INNER JOIN sys.tables AS TBL
            ON TBL.object_id = FK.parent_object_id

UNION ALL

-- UDT references types

SELECT       [EntityName] =
             OBJECT_SCHEMA_NAME(PARAMS.object_id) + N'.' + O.name
            ,[EntityType]        = O.type
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] = PARAM_TYPES.name
            ,[TheReferredType]   = N'UDT'

  FROM      sys.parameters AS PARAMS

            INNER JOIN sys.types AS PARAM_TYPES
            ON PARAMS.user_type_id = PARAM_TYPES.user_type_id
            AND PARAM_TYPES.is_user_defined <> 0

            INNER JOIN sys.objects AS O
            ON O.object_id = PARAMS.object_id

UNION ALL

-- Table, view references partition scheme

SELECT       [EntityName] =
             OBJECT_SCHEMA_NAME(O.object_id) + N'.' + O.name
            ,[EntityType]        = O.type
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] = PS.name
            ,[TheReferredType]   = N'PS'

  FROM      sys.indexes AS IDX

            INNER JOIN sys.partitions AS P
            ON IDX.object_id = P.object_id AND IDX.index_id = P.index_id

            INNER JOIN sys.partition_schemes AS PS
            ON IDX.data_space_id = PS.data_space_id

            INNER JOIN sys.objects AS O
            ON O.object_id = IDX.object_id

UNION ALL

-- Partition scheme references partition function

SELECT       [EntityName]        = PS.name
            ,[EntityType]        = N'PS'
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] =
             OBJECT_SCHEMA_NAME(O.object_id) + N'.' + O.name
            ,[TheReferredType]   = O.type

  FROM      sys.partition_schemes AS PS

            INNER JOIN sys.objects AS O
            ON PS.function_id = O.object_id

UNION ALL

-- Plan guide references sp, udf (and triggers?)

SELECT       [EntityName]        = PG.name
            ,[EntityType]        = N'PG'
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] =
             OBJECT_SCHEMA_NAME(O.object_id) + N'.' + O.name
            ,[TheReferredType]   = O.type

  FROM      sys.objects AS O

            INNER JOIN sys.plan_guides AS PG
            ON PG.scope_object_id = O.object_id

UNION ALL

-- Synonym refrences object

SELECT       [EntityName]        = S.name
            ,[EntityType]        = N'SYN'
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] =
             OBJECT_SCHEMA_NAME(O.object_id) + N'.' + O.name
            ,[TheReferredType]   = O.type

  FROM      sys.objects AS O

            INNER JOIN sys.synonyms AS S
            ON object_id(S.base_object_name) = O.object_id

UNION ALL

-- Sequences that reference UDTTs

SELECT       [EntityName]        = S.name
            ,[EntityType]        = N'SEQ'
            ,[DependencyType]    = N'hard'
            ,[TheReferredEntity] =
             OBJECT_SCHEMA_NAME(O.object_id) + N'.' + O.name
            ,[TheReferredType]   = O.type

  FROM      sys.objects AS O

            INNER JOIN sys.sequences AS S
            ON S.user_type_id = O.object_id

UNION ALL

-- Soft dependencies

SELECT      DISTINCT
             [EntityName] =
             COALESCE(OBJECT_SCHEMA_NAME(SED.Referencing_ID) + N'.', N'') +
             OBJECT_NAME(SED.Referencing_ID)

            ,[EntityType] = REFERENCING_OBJECT.type
            ,[DependencyType] = N'soft'

            ,[TheReferredEntity] =
             COALESCE(REFERENCED_SCHEMA.name + N'.', N'') + --likely schema name
             COALESCE(SED.referenced_entity_name, N'')   --very likely entity name

            ,[TheReferredType] = REFERENCED_OBJECT.type

  FROM      sys.sql_expression_dependencies AS SED

            INNER JOIN sys.objects AS REFERENCING_OBJECT
            ON SED.referencing_id = REFERENCING_OBJECT.object_ID

            INNER JOIN (sys.objects AS REFERENCED_OBJECT

                        INNER join sys.schemas AS REFERENCED_SCHEMA
                        on REFERENCED_OBJECT.schema_id = REFERENCED_SCHEMA.schema_id
                       )
            ON OBJECT_ID(SED.referenced_entity_name) = REFERENCED_OBJECT.object_ID

 WHERE      SED.referencing_Class = 1
   AND      SED.referenced_class = 1
           /*
            * sys.objects (Transact-SQL) type values, via:
            * https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-2017
            */
   AND      REFERENCING_OBJECT.type IN ( N'FN' -- SQL scalar function
                                        ,N'IF' -- SQL inline table-valued function
                                        ,N'P'  -- SQL Stored Procedure
                                        ,N'TF' -- SQL table-valued-function
                                        ,N'TR' -- SQL DML trigger
                                        ,N'U'  -- Table (user-defined)
                                        ,N'V'  -- View
                                       )
;

/*
 * Process the dependencies
 *
 */

DECLARE @RowCount INT

DECLARE @ii INT

-- Firstly we put in the object as a seed.
INSERT INTO @References ( ThePath
                         ,TheFullEntityName
                         ,theType
                         ,iteration
                        )

SELECT       [ThePath]           = COALESCE(OBJECT_SCHEMA_NAME(O.object_id) + N'.', N'') + O.name
            ,[TheFullEntityName] = COALESCE(OBJECT_SCHEMA_NAME(O.object_id) + N'.', N'') + O.name
            ,[theType]           = O.type
            ,[iteration ]        = 1

  FROM      sys.objects AS O

 WHERE      O.name LIKE @ObjectName

-- Then we just pull out the dependencies at each level. watching out for
-- self-references and circular references
SELECT       @rowcount = @@ROWCOUNT
            ,@ii = 2

-- If we are looking for objects on which it depends
IF @ObjectsOnWhichItDepends <> 0

  WHILE @ii < 20 AND @rowcount > 0
  BEGIN
    INSERT INTO @References ( ThePath, TheFullEntityName, theType, iteration )
    SELECT      DISTINCT
                 [ThePath] =
                 PREVIOUS_REFERENCES.ThePath + N'/' + DATABASE_DEPENDENCIES.TheReferredEntity
                ,[TheFullEntityName] = DATABASE_DEPENDENCIES.TheReferredEntity
                ,[theType]           = DATABASE_DEPENDENCIES.TheReferredType
                ,[iteration]         = @ii

      FROM      @DatabaseDependencies AS DATABASE_DEPENDENCIES

                INNER JOIN @References AS PREVIOUS_REFERENCES
                ON PREVIOUS_REFERENCES.TheFullEntityName = DATABASE_DEPENDENCIES.EntityName
                AND PREVIOUS_REFERENCES.iteration = @ii - 1

     WHERE      DATABASE_DEPENDENCIES.TheReferredEntity <> DATABASE_DEPENDENCIES.EntityName
       AND      DATABASE_DEPENDENCIES.TheReferredEntity NOT IN (SELECT RSUB_EXISTING.TheFullEntityName FROM @References RSUB_EXISTING)

    SELECT  @rowcount = @@rowcount
    SELECT  @ii = @ii + 1
  END

ELSE

-- We are looking for objects that depend on it.
WHILE @ii < 20 AND @rowcount > 0
  BEGIN
    INSERT INTO @References ( ThePath
                             ,TheFullEntityName
                             ,theType
                             ,iteration
                            )
    SELECT      DISTINCT
                 [ThePath] =
                 PREVIOUS_REFERENCES.ThePath + N'/' + DATABASE_DEPENDENCIES.EntityName
                ,[TheFullEntityName] = DATABASE_DEPENDENCIES.EntityName
                ,[theType]           = DATABASE_DEPENDENCIES.EntityType
                ,[iteration]         = @ii

      FROM      @DatabaseDependencies AS DATABASE_DEPENDENCIES

                INNER JOIN @References AS PREVIOUS_REFERENCES
                ON PREVIOUS_REFERENCES.TheFullEntityName = DATABASE_DEPENDENCIES.TheReferredEntity
                AND PREVIOUS_REFERENCES.iteration = @ii - 1

     WHERE      DATABASE_DEPENDENCIES.TheReferredEntity <> DATABASE_DEPENDENCIES.EntityName
       AND      DATABASE_DEPENDENCIES.EntityName NOT IN (SELECT RSUB_EXISTING.TheFullEntityName FROM @References RSUB_EXISTING)

    SELECT  @rowcount = @@rowcount
    SELECT  @ii = @ii + 1
  END

RETURN
END
;