Friday, May 10, 2013

Script to Find Foreign Key Columns Missing Index

The below query will retreive all foreign key columns which does not have an Index.
SELECT        s.Name + '.' + t.Name AS TableName,
            c.Name AS ColumnName,
            OBJECT_NAME(constraint_object_id) AS FKName
FROM        sys.foreign_key_columns fkc
LEFT JOIN    sys.index_columns ic
            ON    fkc.parent_object_id = ic.object_id
            AND    fkc.parent_column_id = ic.column_id
LEFT JOIN    sys.columns c
            ON fkc.parent_column_id = c.column_id
            AND fkc.parent_object_id = c.object_id
LEFT JOIN sys.tables t
            ON fkc.parent_object_id = t.object_id
LEFT JOIN sys.schemas s
            ON t.schema_id = s.schema_id
WHERE        ic.object_id IS NULL 
ORDER BY    TableName, ColumnName