Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQL Server SELECT statement to find foreign keys that are missing indexes
SELECT
C.Table_Name,
C.Constraint_Name,
C.Constraint_Columns
FROM
(
SELECT
object_name(i.object_id) table_name, i.name index_name,
MAX(CASE index_column_id when 1 THEN col_name(ic.object_id,ic.column_id) ELSE '' END) +
MAX(CASE index_column_id when 2 THEN col_name(ic.object_id,ic.column_id) ELSE '' END) +
MAX(CASE index_column_id when 3 THEN col_name(ic.object_id,ic.column_id) ELSE '' END) +
MAX(CASE index_column_id when 4 THEN col_name(ic.object_id,ic.column_id) ELSE '' END) +
MAX(CASE index_column_id when 5 THEN col_name(ic.object_id,ic.column_id) ELSE '' END) +
MAX(CASE index_column_id when 6 THEN col_name(ic.object_id,ic.column_id) ELSE '' END) +
MAX(CASE index_column_id when 7 THEN col_name(ic.object_id,ic.column_id) ELSE '' END) +
MAX(CASE index_column_id when 8 THEN col_name(ic.object_id,ic.column_id) ELSE '' END) +
MAX(CASE index_column_id when 9 THEN col_name(ic.object_id,ic.column_id) ELSE '' END) +
MAX(CASE index_column_id when 10 THEN col_name(ic.object_id,ic.column_id) ELSE '' END) index_columns
FROM
sys.index_columns ic, sys.indexes i
WHERE
ic.index_id = i.index_id
AND ic.object_id = i.object_id
AND OBJECTPROPERTY(i.OBJECT_ID,'IsUserTable') = 1
AND i.index_id != 1
GROUP BY
i.object_id, i.name
) AS I
RIGHT OUTER JOIN
(
SELECT kcu.table_name,
kcu.constraint_name,
MAX(CASE kcu.ordinal_position when 1 THEN kcu.column_name ELSE '' END) +
MAX(CASE kcu.ordinal_position when 2 THEN kcu.column_name ELSE '' END) +
MAX(CASE kcu.ordinal_position when 3 THEN kcu.column_name ELSE '' END) +
MAX(CASE kcu.ordinal_position when 4 THEN kcu.column_name ELSE '' END) +
MAX(CASE kcu.ordinal_position when 5 THEN kcu.column_name ELSE '' END) +
MAX(CASE kcu.ordinal_position when 6 THEN kcu.column_name ELSE '' END) +
MAX(CASE kcu.ordinal_position when 7 THEN kcu.column_name ELSE '' END) +
MAX(CASE kcu.ordinal_position when 8 THEN kcu.column_name ELSE '' END) +
MAX(CASE kcu.ordinal_position when 9 THEN kcu.column_name ELSE '' END) +
MAX(CASE kcu.ordinal_position when 10 THEN kcu.column_name ELSE '' END) constraint_columns
FROM
information_schema.key_column_usage kcu, information_schema.referential_constraints rc
WHERE
rc.constraint_name = kcu.constraint_name
GROUP BY
kcu.table_name, kcu.constraint_name
) AS C
ON C.Table_Name = I.Table_Name
AND I.Index_Columns LIKE C.Constraint_Columns + '%'
WHERE
I.Table_Name IS NULL
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment