Skip to content

Instantly share code, notes, and snippets.

@beerose
Created Mar 17, 2021
Embed
What would you like to do?
SELECT
fk.name AS constraint_name,
sch1.name AS [table_schema],
tab1.name AS [table_name],
sch2.name AS [ref_table_schema],
tab2.name AS [ref_table],
(
SELECT
col1.name AS [column],
col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.columns col1
ON col1.column_id = fkc.parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.columns col2
ON col2.column_id = fkc.referenced_column_id AND col2.object_id = tab2.object_id
WHERE fk.object_id = fkc.constraint_object_id
FOR JSON PATH
) AS column_mapping,
fk.delete_referential_action_desc AS [on_delete],
fk.update_referential_action_desc AS [on_update]
FROM sys.foreign_keys fk
INNER JOIN sys.objects obj
ON obj.object_id = fk.referenced_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fk.parent_object_id
INNER JOIN sys.schemas sch1
ON tab1.schema_id = sch1.schema_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fk.referenced_object_id
INNER JOIN sys.schemas sch2
ON tab2.schema_id = sch2.schema_id for json path;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment