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 (reference the same table)


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;