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_KeyFROM sys.foreign_keys fkWHERE parent_object_id = referenced_object_idORDER 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_ActionFROM sys.foreign_keys fkORDER 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 SYSNAMESELECT @Referenced_Object = 'CODE_HR_BRANCH',@Parent_Object = NULLSELECT @@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.NAMEFROM sys.foreign_Key_columns fkcINNER JOIN sys.columns cON fkc.referenced_column_id = c.column_idAND fkc.referenced_object_id = c.object_idINNER JOIN sys.columns crON fkc.parent_column_id = cr.column_idAND fkc.parent_object_id = cr.object_idWHERE fkc.constraint_object_id = fk.OBJECT_IDFOR XML PATH('')), 6, 2000) as FK_Join_TextFROM sys.foreign_keys fkWHERE 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 BYReferenced_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_NAMEFROM Information_Schema.KEY_COLUMN_USAGE ccWHERE cc.CONSTRAINT_NAME = c.CONSTRAINT_NAMEAND cc.TABLE_CATALOG = c.TABLE_CATALOGAND cc.TABLE_SCHEMA = c.TABLE_SCHEMAORDER BY ORDINAL_POSITIONFOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 2, ''), '?') AS FK_ColumnsFROM Information_Schema.TABLE_CONSTRAINTS cWHERE CONSTRAINT_TYPE = 'FOREIGN KEY'AND table_name IS NOT NULLORDER BYTable_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_NameFROM sys.Key_Constraints keysINNER JOIN sys.Index_columns TheColumnsON keys.Parent_Object_ID = TheColumns.Object_IDAND unique_index_ID = index_IDINNER JOIN sys.columns cON TheColumns.object_ID = c.object_IDAND TheColumns.column_ID = c.column_IDWHERE type = 'UQ'AND is_nullable = 1ORDER 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_NAMEFROM Information_Schema.KEY_COLUMN_USAGE ccWHERE cc.CONSTRAINT_NAME=c.CONSTRAINT_NAMEAND cc.TABLE_CATALOG=c.TABLE_CATALOGAND cc.TABLE_SCHEMA=c.TABLE_SCHEMAORDER BY ORDINAL_POSITIONFOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,2,''), '?') AS Constraint_ColumnsFROM Information_Schema.TABLE_CONSTRAINTS cWHERE CONSTRAINT_TYPE IN ('PRIMARY KEY','UNIQUE', 'FOREIGN KEY')AND table_name IS NOT NULLORDER 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 SELECTpk.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 DocumentationFROM sys.objects AS pkLEFT OUTER JOIN sys.extended_properties AS EPON EP.major_id = pk.object_idAND EP.name = 'MS_Description' --the microsoft convention LEFT OUTER JOIN sys.extended_properties AS EPParentON EPParent.major_id = pk.parent_object_idAND EPParent.minor_id = 0AND EPParent.name = 'MS_Description' --the microsoft convention WHERE OBJECTPROPERTY(pk.object_id, 'IsPrimaryKey') = 1;