Skip to content

Instantly share code, notes, and snippets.

@ststeiger
Created September 24, 2024 11:56
Show Gist options
  • Save ststeiger/e4859b4903b6cc9b72b738a0a23f2b1d to your computer and use it in GitHub Desktop.
Save ststeiger/e4859b4903b6cc9b72b738a0a23f2b1d to your computer and use it in GitHub Desktop.
Find multi-column foreign-keys in MSSQL
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