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