Get PK and FK Metadata

Applicability:

                 SQL Server 2000:        Tested
                 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

 A small collection of queries to produce database-level reports on Primary and Foreign Keys.  These should mostly run as-is but can be easily customized.

There are 2 main types of report queries:
  1. Summary List - containing schema/object names and any stored extended properties.  Ideal for documentation
  2. Detail List - containing schema/object names plus object type, component columns and code.  Can be used for documentation, but also valuable for use when constructing Dynamic SQL queries
Where there are multiple variants on the same query, one will utilize the INFORMATION_SCHEMA views and the other the Dynamic Management Views.  This is generally for Detail type queries as some detail levels are only available in one source

Code

Get all FOREIGN KEYS that are Self-Referencing:

 -- get self-referencing FK constraints (refrence the same talbe) 

SELECT @@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,OBJECT_SCHEMA_NAME(fk.Parent_Object_ID) AS Schema_Name ,OBJECT_NAME(fk.Parent_Object_ID) AS Table_Name ,OBJECT_SCHEMA_NAME(fk.Parent_Object_ID) +'.' + OBJECT_NAME(fk.Parent_Object_ID) AS Combined_Name ,Name AS Self_Referencing_Foreign_Key FROM sys.foreign_keys fk WHERE parent_object_id = referenced_object_id ORDER BY Table_Name


Get all FOREIGN KEYS that have cascade actions:

 --get Foreign Key cascade referential actions 

SELECT @@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,OBJECT_SCHEMA_NAME(fk.Parent_Object_ID) AS Schema_Name ,OBJECT_NAME(fk.Parent_Object_ID) AS Table_Name ,OBJECT_SCHEMA_NAME(fk.Parent_Object_ID) +'.' + OBJECT_NAME(fk.Parent_Object_ID) AS Combined_Name ,Name AS FK_Name ,CASE WHEN delete_referential_action = 1 THEN 'DELETE' ELSE 'NONE' END AS Delete_Referential_Action ,CASE WHEN update_referential_action = 1 THEN 'UPDATE' ELSE 'NONE' END AS Update_Referential_Action FROM sys.foreign_keys fk ORDER BY Table_Name


Get Detail list of FOREIGN KEYS in a Database including the TSQL to implement the join:

 DECLARE

@Referenced_Object SYSNAME ,@Parent_Object SYSNAME SELECT @Referenced_Object = 'CODE_HR_BRANCH' ,@Parent_Object = NULL SELECT @@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,OBJECT_SCHEMA_NAME(fk.Parent_Object_ID) AS Parent_Schema_Name ,OBJECT_NAME(fk.Parent_Object_ID) AS Parent_Table_Name ,OBJECT_SCHEMA_NAME(fk.Parent_Object_ID) +'.' + OBJECT_NAME(fk.Parent_Object_ID) AS Parent_Combined_Name ,OBJECT_SCHEMA_NAME(fk.referenced_object_ID) AS Referenced_Schema_Name ,OBJECT_NAME(fk.referenced_object_ID) AS Referenced_Table_Name ,OBJECT_SCHEMA_NAME(fk.referenced_object_ID) +'.' + OBJECT_NAME(fk.referenced_object_ID) AS Referenced_Combined_Name ,fk.name AS FK_Name ,SUBSTRING ( ( SELECT ' AND ' + OBJECT_SCHEMA_NAME(fk.Parent_object_ID) + '.' + OBJECT_NAME(fk.Parent_object_ID) + '.' + cr.name + ' = ' + OBJECT_SCHEMA_NAME(fkc.referenced_object_id) + '.' + OBJECT_NAME(fkc.referenced_object_id) + '.' + c.NAME FROM sys.foreign_Key_columns fkc INNER JOIN sys.columns c ON fkc.referenced_column_id = c.column_id AND fkc.referenced_object_id = c.object_id INNER JOIN sys.columns cr ON fkc.parent_column_id = cr.column_id AND fkc.parent_object_id = cr.object_id WHERE fkc.constraint_object_id = fk.OBJECT_ID FOR XML PATH('') ), 6, 2000 ) as FK_Join_Text FROM sys.foreign_keys fk WHERE fk.referenced_object_ID = ISNULL(OBJECT_ID(@Referenced_Object),fk.referenced_object_ID) AND fk.parent_object_id = ISNULL(OBJECT_ID(@Parent_Object), fk.parent_object_id) ORDER BY Referenced_Table_Name ,Parent_Table_Name ,FK_Name


Get summary list of FOREIGN KEYS with comma-separated list of FK columns for each:

 SELECT

@@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,c.TABLE_SCHEMA AS Schema_Name ,c.TABLE_NAME AS Table_Name ,c.TABLE_SCHEMA +'.' + c.TABLE_NAME AS Combined_Name ,c.CONSTRAINT_NAME AS FK_Constraint_Name ,COALESCE ( STUFF ( ( SELECT ', ' + cc.COLUMN_NAME FROM Information_Schema.KEY_COLUMN_USAGE cc WHERE cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME AND cc.TABLE_CATALOG = c.TABLE_CATALOG AND cc.TABLE_SCHEMA = c.TABLE_SCHEMA ORDER BY ORDINAL_POSITION FOR XML PATH(''), TYPE ).value('.', 'varchar(max)'), 1, 2, '' ), '?' ) AS FK_Columns FROM Information_Schema.TABLE_CONSTRAINTS c WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND table_name IS NOT NULL ORDER BY Table_Name ,FK_Constraint_Name


Get all PRIMARY KEYS that are enforced by a UNIQUE Constraint that is nullable:

 -- find primary keys that are enforced by UNIQUE indexes that are nullable 

SELECT DISTINCT @@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,OBJECT_SCHEMA_NAME(keys.Parent_Object_ID) AS Schema_Name ,OBJECT_NAME(keys.Parent_Object_ID) AS Table_Name ,OBJECT_SCHEMA_NAME(keys.Parent_Object_ID) +'.' + OBJECT_NAME(keys.Parent_Object_ID) AS Combined_Name FROM sys.Key_Constraints keys INNER JOIN sys.Index_columns TheColumns ON keys.Parent_Object_ID = TheColumns.Object_ID AND unique_index_ID = index_ID INNER JOIN sys.columns c ON TheColumns.object_ID = c.object_ID AND TheColumns.column_ID = c.column_ID WHERE type = 'UQ' AND is_nullable = 1 ORDER BY Table_Name


Get all PRIMARY KEYS and UNIQUE Constraints with comma-separated list of FK columns for each:

 -- only identifies table level constraints PK, FK, UQ 

SELECT @@SERVERNAME AS Server_Name ,DB_NAME() AS Database_Name ,c.TABLE_SCHEMA AS Schema_Name ,c.TABLE_NAME AS Table_Name ,c.TABLE_SCHEMA + '.' + c.TABLE_NAME AS Combined_Name ,Constraint_TYPE AS Contraint_Type ,c.CONSTRAINT_NAME AS Constraint_Name ,COALESCE ( STUFF ( ( SELECT ', ' + cc.COLUMN_NAME FROM Information_Schema.KEY_COLUMN_USAGE cc WHERE cc.CONSTRAINT_NAME=c.CONSTRAINT_NAME AND cc.TABLE_CATALOG=c.TABLE_CATALOG AND cc.TABLE_SCHEMA=c.TABLE_SCHEMA ORDER BY ORDINAL_POSITION FOR XML PATH (''), TYPE ).value('.', 'varchar(max)'),1,2,'' ), '?' ) AS Constraint_Columns FROM Information_Schema.TABLE_CONSTRAINTS c WHERE CONSTRAINT_TYPE IN ('PRIMARY KEY','UNIQUE', 'FOREIGN KEY') AND table_name IS NOT NULL ORDER BY Table_Name


Get summary list of PRIMARY KEYS in a Database:

 /* The Primary Keys */

--all primary keys in the database and their parent table SELECT pk.name AS Primary_Key_Name ,OBJECT_SCHEMA_NAME(pk.parent_object_id) + '.' + OBJECT_NAME(pk.parent_object_id) AS Parent_Object_Name ,COALESCE(EP.value, '') AS KeyDoc, COALESCE(EPParent.value, '') AS Documentation FROM sys.objects AS pk LEFT OUTER JOIN sys.extended_properties AS EP ON EP.major_id = pk.object_id AND EP.name = 'MS_Description' --the microsoft convention LEFT OUTER JOIN sys.extended_properties AS EPParent ON EPParent.major_id = pk.parent_object_id AND EPParent.minor_id = 0 AND EPParent.name = 'MS_Description' --the microsoft convention WHERE OBJECTPROPERTY(pk.object_id, 'IsPrimaryKey') = 1;