Get Referenced Object Hierarchy

Applicability:

                 SQL Server 2000:        N/A
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Tested
                 SQL Server 2014:        Tested
                 SQL Server 2016:        Tested
                 SQL Server 2017:        Not Tested          

Credits:

Author:Phil Factor/ChillyDBA
Date:    5 Jun 2020

Description

2 queries to produce a full list of object references/hierarchy.
The first query is a detail list with one relationship per line
The second query has all the referenced object names pivoted into an ordered comma separated list column

Code

Get a detail list of all object references:


DECLARE @Object_Name SYSNAME ,@Object_Type SYSNAME ,@NestLevel INT SELECT @Object_Name = NULL ,@Object_Type = NULL /* AGGREGATE_FUNCTION CHECK_CONSTRAINT CLR_SCALAR_FUNCTION CLR_STORED_PROCEDURE CLR_TABLE_VALUED_FUNCTION CLR_TRIGGER DEFAULT_CONSTRAINT EXTENDED_STORED_PROCEDURE FOREIGN_KEY_CONSTRAINT INTERNAL_TABLE PLAN_GUIDE PRIMARY_KEY_CONSTRAINT REPLICATION_FILTER_PROCEDURE RULE SEQUENCE_OBJECT SERVICE_QUEUE SQL_INLINE_TABLE_VALUED_FUNCTION SQL_SCALAR_FUNCTION SQL_STORED_PROCEDURE SQL_TABLE_VALUED_FUNCTION SQL_TRIGGER SYNONYM SYSTEM_TABLE TABLE_TYPE UNIQUE_CONSTRAINT USER_TABLE VIEW */ ,@NestLevel = 0 --SELECT @Object_name = '' ;WITH DepTree ( Referenced_Object_ID ,Referenced_Object_Schema ,Referenced_Object_Name ,Referenced_Object_Type ,Referencing_Object_ID ,Referencing_Object_Schema ,Referencing_Object_Name ,Referencing_Object_Type ,NestLevel ) AS ( SELECT o.[object_id] AS Referenced_Object_ID ,OBJECT_SCHEMA_NAME(o.[object_id]) AS Referenced_Object_Schema ,o.name AS Referenced_Object_Name ,o.type_desc AS Referenced_Object_Type ,o.[object_id] AS Referencing_Object_ID ,OBJECT_SCHEMA_NAME(o.[object_id]) AS Referencing_Object_Schema ,o.name AS Referencing_Object_Name ,o.type_desc AS Referencing_Object_Type ,0 AS NestLevel FROM sys.objects o WHERE o.name = ISNULL(@Object_name, o.name) AND o.type_desc = ISNULL(@Object_Type, o.type_desc) UNION ALL SELECT d1.[referenced_id] AS Referenced_Object_ID ,OBJECT_SCHEMA_NAME(so1.[object_id]) AS Referenced_Object_Schema ,so1.name AS Referenced_Object_Name ,so1.type_desc AS Referenced_Object_Type ,d1.referencing_id AS Referencing_Object_ID ,OBJECT_SCHEMA_NAME(so2.[object_id]) AS Referencing_Object_Schema ,so2.name AS Referencing_Object_Name ,so2.type_desc AS Referencing_Object_Type ,NestLevel + 1 AS NestLevel FROM sys.sql_expression_dependencies d1 INNER JOIN DepTree r ON d1.referenced_id = r.Referencing_Object_ID INNER JOIN sys.objects so1 ON d1.referenced_id = so1.[object_id] INNER JOIN sys.objects so2 ON d1.referencing_id = so2.[object_id] ) SELECT DISTINCT @@SERVERNAME AS ServerName ,DB_NAME() AS DatabaseName ,Referenced_Object_ID ,Referenced_Object_Schema ,Referenced_Object_Name ,Referenced_Object_Type ,Referencing_Object_ID ,Referencing_Object_Schema ,Referencing_Object_Name ,Referencing_Object_Type ,NestLevel FROM DepTree WHERE NestLevel > @NestLevel ORDER BY Referenced_Object_Type ,Referenced_Object_Name ,NestLevel





Get a summary list of all object references:


DECLARE @Object_Name SYSNAME ,@Object_Type SYSNAME SELECT @Object_Name = NULL ,@Object_Type = NULL /* AGGREGATE_FUNCTION CHECK_CONSTRAINT CLR_SCALAR_FUNCTION CLR_STORED_PROCEDURE CLR_TABLE_VALUED_FUNCTION CLR_TRIGGER DEFAULT_CONSTRAINT EXTENDED_STORED_PROCEDURE FOREIGN_KEY_CONSTRAINT INTERNAL_TABLE PLAN_GUIDE PRIMARY_KEY_CONSTRAINT REPLICATION_FILTER_PROCEDURE RULE SEQUENCE_OBJECT SERVICE_QUEUE SQL_INLINE_TABLE_VALUED_FUNCTION SQL_SCALAR_FUNCTION SQL_STORED_PROCEDURE SQL_TABLE_VALUED_FUNCTION SQL_TRIGGER SYNONYM SYSTEM_TABLE TABLE_TYPE UNIQUE_CONSTRAINT USER_TABLE VIEW */ SELECT @@SERVERNAME AS Referenced_Server ,DB_NAME() AS Referenced_Database ,o.type_desc AS Referenced_Object_Type ,d1.referenced_entity_name AS Referenced_Object_Name ,d1.referenced_id AS Referenced_Object_ID ,STUFF ( ( SELECT ', ' + OBJECT_NAME(d2.referencing_id) FROM sys.sql_expression_dependencies d2 WHERE d2.referenced_id = d1.referenced_id ORDER BY OBJECT_NAME(d2.referencing_id) FOR XML PATH('') ), 1, 1, '' ) AS Dependent_Objects_List FROM sys.sql_expression_dependencies d1 JOIN sys.objects o ON d1.referenced_id = o.[object_id] WHERE d1.referenced_entity_name = ISNULL(@Object_Name, d1.referenced_entity_name) AND o.type_desc = ISNULL(@Object_Type, o.type_desc) GROUP BY o.type_desc, d1.referenced_id, d1.referenced_entity_name ORDER BY o.type_desc, d1.referenced_entity_name






ċ
Andy Hughes,
Jun 5, 2020, 5:10 PM
ċ
Andy Hughes,
Jun 5, 2020, 5:10 PM
Comments