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 SYSNAMESELECT @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_NameFROM sys.sql_expression_dependencies sedINNER JOIN sys.objects oON referencing_ID=o.object_IDWHERE is_schema_bound_reference=0AND OBJECT_NAME(sed.referenced_id) = ISNULL(@Referenced_Object, OBJECT_NAME(sed.referenced_id))ORDER BY Referenced_Table_Name, Referencing_Object_Name