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




Comments