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

Firstly: list the columns with a foreign key constraint. This will help:

http://stackoverflow.com/questions/831589/query-to-get-all-foreign-key-constraints-in-sqlserver-2000

Cross-compare with sysindexes and syscolumns tables; the keys field in sysindexes has a list of all keys in the index.

Answer 2

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
                )
        )

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.

Answer 3

Here is an answer that works for SQL Server 2000 authored by a co-worker:

/*
Description:
    This script outputs a table with all the current database un-indexed foreign keys.

    The table has three columns ( TableName , ColumnName, ForeignKeyName ) 
    TableName: The table containing the un-indexed foreign key
    ColumnName: The foreign key column that’s not indexed 
    ForeignKeyName: Name of foreign key witch column doesn’t have an index 
    */
DECLARE 
    @TableName varchar(255),
    @ColumnName varchar(255),
    @ForeignKeyName sysname

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE FKColumns_cursor CURSOR Fast_Forward FOR
SELECT  cu.TABLE_NAME, cu.COLUMN_NAME, cu.CONSTRAINT_NAME
FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS ic 
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu ON ic.CONSTRAINT_NAME = cu.CONSTRAINT_NAME
WHERE   ic.CONSTRAINT_TYPE = 'FOREIGN KEY'

CREATE TABLE #temp1(    
    TableName varchar(255),
    ColumnName varchar(255),
    ForeignKeyName sysname
)

OPEN FKColumns_cursor  
FETCH NEXT FROM FKColumns_cursor INTO @TableName, @ColumnName, @ForeignKeyName

WHILE @@FETCH_STATUS = 0  
BEGIN

    IF ( SELECT COUNT(*)
    FROM sysobjects o  
     INNER JOIN sysindexes x ON x.id = o.id
     INNER JOIN  syscolumns c ON o.id = c.id 
     INNER JOIN sysindexkeys xk ON c.colid = xk.colid AND o.id = xk.id AND x.indid = xk.indid
    WHERE o.type in ('U')
     AND xk.keyno <= x.keycnt
     AND permissions(o.id, c.name) <> 0
     AND (x.status&32) = 0
     AND o.name = @TableName
     AND c.name = @ColumnName
    ) = 0
    BEGIN
     INSERT INTO #temp1 SELECT @TableName, @ColumnName, @ForeignKeyName
    END


    FETCH NEXT FROM FKColumns_cursor INTO @TableName, @ColumnName, @ForeignKeyName
END  
CLOSE FKColumns_cursor  
DEALLOCATE FKColumns_cursor 

SELECT * FROM #temp1 ORDER BY TableName