Get PK and FK Metadata

-- 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 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

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

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

-- 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

-- 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

/* 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;