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

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