Query to Recursively Identify Object Dependencies

DESCRIPTION

Wrote this Stored Procedure below which RECURSIVELY lists all the dependent child objects and child's dependent objects and child's child...etc. The input parameter can be Stored Proc, User Function, View.Can easily be altered to get a Unique List of Column 5, regardless of what Level the Object was called and how deep and by which object.

COLUMNS

1. UsedByObjectId - The parent object that uses the dependent object
2. UsedByObjectName - The name of the parent object
3. UsedByObjectType - Type of the parent object (P,V,FN)
4. DependentObjectId - The child object the parent uses
5. DependentObjectName - Name of the child object
6. DependentObjectType - Type of the dependent child object (P,V,FN, U)
7. Level - How deep, the nested recursive level which the object is used

THE CODE

--=========================================================================--=========================================================================--== utlGetAllDependentObjectsRecursive - Uses recursive common table--==     expression to recursively get all the dependent objects as well--==     as the child objects and child's child objects of a --==     Stored Procedure or View or Function.  can be easily modified to --==     include all other types of Objects--=========================================================================--=========================================================================CREATE PROCEDURE utlGetAllDependentObjectsRecursive(   -- Supports Stored Proc, View, User Function, User Table   @PARAM_OBJECT_NAME VARCHAR(500))ASBEGIN    WITH CTE_DependentObjects AS    (        SELECT DISTINCT         b.object_id AS UsedByObjectId,         b.name AS UsedByObjectName, b.type AS UsedByObjectType,         c.object_id AS DependentObjectId,         c.name AS DependentObjectName , c.type AS DependenObjectType        FROM  sys.sysdepends a        INNER JOIN sys.objects b ON a.id = b.object_id        INNER JOIN sys.objects c ON a.depid = c.object_id        WHERE b.type IN ('P','V', 'FN') AND c.type IN ('U', 'P', 'V', 'FN')     ),    CTE_DependentObjects2 AS    (       SELECT           UsedByObjectId, UsedByObjectName, UsedByObjectType,          DependentObjectId, DependentObjectName, DependenObjectType,           1 AS Level       FROM CTE_DependentObjects a       WHERE a.UsedByObjectName = @PARAM_OBJECT_NAME       UNION ALL        SELECT           a.UsedByObjectId, a.UsedByObjectName, a.UsedByObjectType,          a.DependentObjectId, a.DependentObjectName, a.DependenObjectType,           (b.Level + 1) AS Level       FROM CTE_DependentObjects a       INNER JOIN  CTE_DependentObjects2 b           ON a.UsedByObjectName = b.DependentObjectName    )    SELECT DISTINCT * FROM CTE_DependentObjects2     ORDER BY Level, DependentObjectName    END 

I saw this post to identify all the objects that reference a particular synonym and used the base logic in the answer in a recursive CTE to identify all the objects related to a comma-delimited list of the objects within the top level query being executed.

Declare @baseObjects Nvarchar(1000) = '[Schema].[Table],[Schema].[View],[Schema].[Function],[Schema].[StoredProc]',        @SQL Nvarchar(Max);Declare @objects Table (SchemaName Varchar(512), TableName Varchar(512), ID Int, xtype Varchar(10));Set     @SQL = 'Select  ss.name As SchemaName,                        so.name As TableName,                        so.id,                        so.xtype                From    sysobjects so                Join    sys.schemas ss                        On  so.uid = ss.schema_id                Where   so.id In (Object_ID(''' + Replace(@baseObjects,',','''),Object_ID(''') + '''))';Insert  @objectsExec    sp_executeSQL @SQL;With    test As(        Select  ss.name As SchemaName,                so.name As TableName,                so.id,                so.xtype        From    sys.sql_expression_dependencies sed        Join    @objects vo                On  sed.referencing_id = vo.ID        Join    sysobjects so                On  sed.referenced_id = so.id        Join    sys.schemas ss                On  so.uid = ss.schema_id        Union   All        Select  ss.name As SchemaName,                so.name As TableName,                so.id,                so.xtype        From    test        Join    sys.sql_expression_dependencies sed                On  sed.referencing_id = test.id                And sed.referencing_id <> sed.referenced_id        Join    sysobjects so                On  sed. referenced_id = so.id        Join    sys.schemas ss                On  so.uid = ss.schema_id)Select  Distinct *From    testUnionSelect  *From    @objects;

Check This one, You will get all recursive objects.

    WITH Refobjects (referencing_object_name,referencing_object_type_desc)     AS     (        SELECT        o.name AS referencing_object_name,        o.type_desc AS referencing_object_type_desc        FROM        sys.sql_expression_dependencies sed        INNER JOIN        sys.objects o ON sed.referencing_id = o.[object_id]        WHERE        sed.referenced_entity_name = 'Your Object Name'      UNION ALL         SELECT            o.name AS referencing_object_name,            o.type_desc AS referencing_object_type_desc        FROM            sys.sql_expression_dependencies sed            INNER JOIN            sys.objects o ON sed.referencing_id = o.[object_id]            INNER JOIN Refobjects ON sed.referenced_entity_name = Refobjects.referencing_object_name     )    SELECT distinct * FROM Refobjects    Order by 2 desc,1 ;