Show Referential Integrity Relationship Constraints and Rules

The following commands show referential foreignkey/primary key constraints and rules that are defined in the database. The first command uses the INFORMATION_SCHEMA tables and thus may work in multiple DBMS vendors. It also provides an ALTER TABLE command that could recreate the relationship. The second command is based on SQL Server tables, but can be easily adpted to other DBMSs. Both use the STRING_AGG function to aggregate the column names for compound keys.

--Show referential constraints and rules.
SELECT RC.CONSTRAINT_NAME, MATCH_OPTION, UPDATE_RULE, DELETE_RULE
,KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT
,KCU1.TABLE_SCHEMA AS FK_SCHEMA 
,KCU1.TABLE_NAME AS FK_TABLE
,FK_COLUMNS
,RC.UNIQUE_CONSTRAINT_NAME AS PK_CONSTRAINT
,KCU2.TABLE_SCHEMA AS PK_SCHEMA 
,KCU2.TABLE_NAME AS PK_TABLE
,PK_COLUMNS
,'ALTER TABLE '+KCU1.TABLE_SCHEMA+'.'+KCU1.TABLE_NAME+' ADD CONSTRAINT '+RC.CONSTRAINT_NAME
+' FOREIGN KEY ('+FK_COLUMNS+') REFERENCES '+KCU2.TABLE_SCHEMA+'.'+KCU2.TABLE_NAME+' ('+PK_COLUMNS
+') ON DELETE '+DELETE_RULE+' ON UPDATE '+UPDATE_RULE+';' AS COMMAND
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 
JOIN (
	SELECT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME
	,STRING_AGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY ORDINAL_POSITION) AS FK_COLUMNS
	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
	GROUP BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME
) AS KCU1 
ON  KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
JOIN (
	SELECT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME
	,STRING_AGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY ORDINAL_POSITION) AS PK_COLUMNS
	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
	GROUP BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME
) AS KCU2
ON  KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 

 --Show PK/FK relationships Referential Integrity		
SELECT FK.name	ForeignKeyName
, OBJECT_NAME(FK.referenced_object_id)	ParentTable
, OBJECT_NAME(FK.parent_object_id)	ChildTable
, STRING_AGG(PC.Name,', ')	PrimaryColumns
, STRING_AGG(FC.Name,', ')	ForeignColumns
, is_disabled, is_not_trusted, delete_referential_action_desc, update_referential_action_desc
FROM SYS.FOREIGN_KEYS FK
JOIN SYS.FOREIGN_KEY_COLUMNS FKC
ON FKC.constraint_object_id = FK.object_id
JOIN SYS.COLUMNS PC
ON PC.Object_id = FKC.referenced_object_id
AND PC.Column_id = FKC.referenced_column_id
JOIN SYS.COLUMNS FC
ON FC.Object_id = FKC.parent_object_id
AND FC.Column_id = FKC.parent_column_id
--WHERE OBJECT_NAME(FK.referenced_object_id) IN ('LM04')
GROUP BY FK.Name, FK.parent_object_id, FK.referenced_object_id
, is_disabled, is_not_trusted, delete_referential_action_desc, update_referential_action_desc
ORDER BY FK.referenced_object_id, FK.Name