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 INTSELECT @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(SELECTo.[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 NestLevelFROM sys.objects oWHERE o.name = ISNULL(@Object_name, o.name)AND o.type_desc = ISNULL(@Object_Type, o.type_desc)UNION ALLSELECTd1.[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 NestLevelFROM sys.sql_expression_dependencies d1INNER JOIN DepTree rON d1.referenced_id = r.Referencing_Object_IDINNER JOIN sys.objects so1ON d1.referenced_id = so1.[object_id]INNER JOIN sys.objects so2ON 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,NestLevelFROM DepTreeWHERE NestLevel > @NestLevelORDER BYReferenced_Object_Type,Referenced_Object_Name,NestLevel

Get a summary list of all object references:

DECLARE @Object_Name SYSNAME,@Object_Type SYSNAMESELECT @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 d2WHERE d2.referenced_id = d1.referenced_idORDER BY OBJECT_NAME(d2.referencing_id)FOR XML PATH('')), 1, 1, '') AS Dependent_Objects_ListFROM sys.sql_expression_dependencies d1 JOIN sys.objects oON 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_nameORDER BY o.type_desc, d1.referenced_entity_name