Created
March 17, 2021 11:41
-
-
Save beerose/ef1762e4b2a2fa56fbc2b027425b783e to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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