Query to Recursively Identify Object Dependencies

Question

I have a complex query with several tables, views and functions within it. The functions and views split off into more views and functions that potentially split off into more views and functions within them.

This query is having performance issues so I want to get a clear and concise list of all the objects that are being referenced within my query so I have a basis for my investigation. How do I get this list of objects?

Answer 1

I have improved above answers as none was working for me. I needed a way to refresh complex nested objects by type sp_refreshsqlmodule. You need to update and <TYPE_NAME> to your own.

Following method work for me:

WITH cRefobjects AS (

  SELECT o.name, s.name AS sch, o.type, 1 AS level
  FROM sys.sql_expression_dependencies sed
    INNER JOIN sys.objects o ON o.object_id = sed.referencing_id
    INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
  WHERE (sed.referenced_schema_name = '<SCHEMA>' OR sed.referenced_schema_name IS NULL)
  AND  sed.referenced_entity_name = '<TYPE_NAME>'

UNION ALL 

  SELECT o.name, s.name AS sch, o.type, cRefobjects.level + 1 AS level
  FROM
    sys.sql_expression_dependencies AS sed
    INNER JOIN sys.objects o ON o.object_id = sed.referencing_id
    INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
    INNER JOIN cRefobjects ON sed.referenced_entity_name = cRefobjects.name
                           AND sed.referenced_schema_name = cRefobjects.sch
  )

    SELECT name, MAX(level) as level,  'EXEC sys.sp_refreshsqlmodule @name = ''' + sch + '.' + name + '''', type
    FROM cRefobjects
    GROUP BY name, sch, type
    ORDER BY level, type, name;

Answer 2

Based on @Raju Chavan's answer above, which works great. However...

I've added support for schemas, as well as returning (and ordering by) the recursion level, so one can easily turn it into a script to refresh referencing objects in the correct order, using sp_refreshsqlmodule (see point 3 below).

WITH 
  cRefobjects AS (

      SELECT o.name, s.name AS sch, o.type_desc, 1 AS level
      FROM sys.sql_expression_dependencies sed
        INNER JOIN sys.objects o ON o.object_id = sed.referencing_id
        INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
      WHERE (sed.referenced_schema_name = '<your schema>' OR sed.referenced_schema_name IS NULL)
      AND  sed.referenced_entity_name = '<your object name>'

    UNION ALL 

      SELECT o.name, s.name AS sch, o.type_desc, cRefobjects.level + 1 AS level
      FROM
        sys.sql_expression_dependencies AS sed
        INNER JOIN sys.objects o ON o.object_id = sed.referencing_id
        INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
        INNER JOIN cRefobjects ON sed.referenced_entity_name = cRefobjects.name
                               AND sed.referenced_schema_name = cRefobjects.sch
  )

SELECT DISTINCT name, sch, type_desc, level 
FROM cRefobjects
ORDER BY level, type_desc DESC, name;

A few things to consider:

1. Replace <your schema> and <your object> with with what you require.
2. When the referenced object does not have a schema prefix in the referencing object, the schema is actually unknown, hence the predicate above OR sed.referenced_schema_name IS NULL. This may get the wrong referencing object if you're not following best practices in your database objects.
3. My goal in seeking and finding this answer was to write a script to refresh referenced objects in the database automatically after editing a view, using sp_refreshsqlmodule. To do this, just wrap the CTE shown above as follows. This prints the required SQL for refreshing the referencing objects in the correct order:

DECLARE @SQL NVARCHAR(4000); SET @SQL = '';
WITH 
  cRefobjects AS (
    ...
  )
--SELECT DISTINCT name, sch, type_desc, level 
SELECT @SQL = @SQL + 'EXEC sys.sp_refreshsqlmodule ''' + sch + '.' + name + '''' + CHAR(13)+CHAR(10)
FROM cRefobjects
ORDER BY level, type_desc DESC, name;

PRINT @SQL

Answer 3

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 ;

Answer 4

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)
)
AS
BEGIN
    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 

Answer 5

In SQL Server 2008 there are two new Dynamic Management Functions introduced to keep track of object dependencies: sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities:

1/ Returning the entities that refer to a given entity:

SELECT
        referencing_schema_name, referencing_entity_name, 
        referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('<TableName>', 'OBJECT')

2/ Returning entities that are referenced by an object:

SELECT
        referenced_schema_name, referenced_entity_name, referenced_minor_name, 
        referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('<StoredProcedureName>', 'OBJECT');

Another option is to use a pretty useful tool called SQL Dependency Tracker from Red Gate.

Answer 6

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  @objects
Exec    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    test
Union
Select  *
From    @objects;