Created
September 24, 2024 11:56
-
-
Save ststeiger/e4859b4903b6cc9b72b738a0a23f2b1d to your computer and use it in GitHub Desktop.
Find multi-column foreign-keys in MSSQL
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 | |
sch.name AS table_schema | |
,tp.name AS table_name | |
,schFK.name AS fk_schema | |
,fk.name AS fk_name | |
,STUFF | |
( | |
( | |
SELECT ', ' + c.name | |
FROM sys.foreign_key_columns AS fkc | |
INNER JOIN sys.columns AS c | |
ON fkc.parent_column_id = c.column_id | |
AND fkc.parent_object_id = c.object_id | |
WHERE fkc.constraint_object_id = fk.object_id | |
FOR XML PATH('') | |
) | |
, 1, 2, '' | |
) AS columns | |
,COUNT(fkc.constraint_column_id) AS cnt | |
FROM sys.foreign_keys AS fk | |
INNER JOIN sys.schemas AS schFK ON schFK.schema_id = fk.schema_id | |
INNER JOIN sys.tables AS tp ON fk.parent_object_id = tp.object_id | |
INNER JOIN sys.schemas AS sch ON sch.schema_id = tp.schema_id | |
INNER JOIN sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id | |
GROUP BY sch.name, tp.name, schFK.name, fk.name, fk.object_id | |
HAVING COUNT(fkc.constraint_column_id) > 1 | |
ORDER BY tp.name, fk.name; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment