How can I find unindexed foreign keys in SQL Server

Question

I have a SQL Server 2000 database with approximately 220 tables. These tables have a number foreign key relationships between them. Through performance analysis, we've discovered a number of these foreign key relationships are missing indexes. Instead of being reactive to performance problems, I'd like to be pro-active and find all foreign keys that are missing indexes.

How can I programmatically determine which foreign key are missing indexes?

Answer 1

SELECT  *
FROM    sys.foreign_keys fk
WHERE   EXISTS
        (
        SELECT  *
        FROM    sys.foreign_key_columns fkc
        WHERE   fkc.constraint_object_id = fk.object_id
                AND NOT EXISTS
                (
                SELECT  *
                FROM    sys.index_columns ic
                WHERE   ic.object_id = fkc.parent_object_id
                        AND ic.column_id = fkc.parent_column_id
                        AND ic.index_column_id = fkc.constraint_column_id
                )
        )

I don't have a copy of SQL Server 2000 handy, but you may need to change sys.foreign_key to sysforeignkeys etc., like described here.

This query selects all foreign keys which don't have an index covering all columns that comprise the key.

This supports multi-column foreign keys just as well.

This, however, will return a false positive if there is a composite index that covers all columns but they are not the leftmost columns in this index.

Like, if there is a FOREIGN KEY (col2, col3) and an index on (col1, col2, col3), this will return that there is an index despite the fact this index is unusable for this foreign key.