Find Unindexed Foreign Keys

This script will find foreign keys (on referencing table) that are not indexed.  It looks for exact definition of index matching columns and order. It will also give you the Create Index Script.



SELECT  DISTINCT      /* remove dups caused by composite constraints */
        DB_NAME()                                   AS "database_name",
        OBJECT_NAME(foreign_keys.parent_object_id)  AS "table_name",
        foreign_keys."name"                         AS "fk_name"
        , 'CREATE NONCLUSTERED INDEX IX_' + CONVERT(NVARCHAR, OBJECT_NAME(foreign_keys.parent_object_id)) + '_' + CONVERT(NVARCHAR, O.NAME) + ' ON ' + CONVERT(NVARCHAR, 
        OBJECT_NAME(foreign_keys.parent_object_id)) + ' (' + CONVERT(NVARCHAR, O.NAME) + ' ASC)' AS [Index_Script] 
FROM    sys.foreign_keys                            AS foreign_keys
JOIN sys.foreign_key_columns                     AS foreign_key_columns
  ON foreign_keys."object_id" = foreign_key_columns.constraint_object_id
INNER JOIN Sys.Columns AS O ON O.Column_ID = foreign_key_columns.Parent_Column_ID AND O.Object_ID = foreign_key_columns.Parent_Object_ID 
WHERE NOT EXISTS (
SELECT 'An index with same columns and column order'
FROM    sys.indexes                                 AS indexes
JOIN sys.index_columns AS index_columns
 ON indexes."object_id" = index_columns."object_id"
WHERE foreign_keys.parent_object_id = indexes."object_id"
 AND indexes.index_id = index_columns.index_id
 AND foreign_key_columns.constraint_column_id = index_columns.key_ordinal
 AND foreign_key_columns.parent_column_id = index_columns.column_id
 AND   OBJECTPROPERTYEX(indexes."object_id",'IsMSShipped') = 0
 AND   indexes.is_hypothetical = 0
)


 AND    foreign_keys.is_ms_shipped = 0 ORDER BY OBJECT_NAME(foreign_keys.parent_object_id);