Get all Database DRI

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: 4 Jun 2020

Description

Lists all Declarative Referential Integrity for a single Database

Code

--get all objects referencing one or all table

DECLARE

@Referenced_Object SYSNAME

SELECT

@Referenced_Object = NULL--'code_country'

SELECT

@@SERVERNAME AS Server_Name

,DB_NAME() AS Database_Name

,OBJECT_SCHEMA_NAME(sed.referenced_id) AS Referenced_Schema_Name

,OBJECT_NAME(sed.referenced_id) AS Referenced_Table_Name

,OBJECT_SCHEMA_NAME(sed.referenced_id) +'.' + OBJECT_NAME(sed.referenced_id) AS Referenced_Combined_Name

,OBJECT_SCHEMA_NAME(sed.Referencing_ID) AS Referencing_Schema_Name

,OBJECT_NAME(sed.Referencing_ID) AS Referencing_Object_Name

,o.type_desc AS Referencing_Object_Type

,OBJECT_SCHEMA_NAME(sed.Referencing_ID) +'.' + OBJECT_NAME(sed.Referencing_ID) AS Referencing_Combined_Name

FROM sys.sql_expression_dependencies sed

INNER JOIN sys.objects o

ON referencing_ID=o.object_ID

WHERE is_schema_bound_reference=0

AND OBJECT_NAME(sed.referenced_id) = ISNULL(@Referenced_Object, OBJECT_NAME(sed.referenced_id))

ORDER BY Referenced_Table_Name, Referencing_Object_Name