SQL Server SELECT statement to find foreign keys that are missing indexes

sql-server-find-missing-indexes.sql

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