Foreign Keys without Indexes

Here are some scripts that provide index creation statements for foreign keys without indexes. NB : I am not advocating creating indexes on every foreign key. Their use depends on application design, (the sql it runs) and whether other covering indexes are present. SQL 2000 Version

/*
adapted from http://stackoverflow.com/questions/1406119/how-can-i-find-unindexed-foreign-keys-in-sql-server
Uses my NCI_tablename-indexname index naming convention
*/

DECLARE 
    @SchemaName varchar(255),
    @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_SCHEMA, 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(    
    SchemaName varchar(255),
    TableName varchar(255),
    ColumnName varchar(255),
    ForeignKeyName sysname
)

OPEN FKColumns_cursor  
FETCH NEXT FROM FKColumns_cursor INTO @SchemaName,@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 @SchemaName, @TableName, @ColumnName, @ForeignKeyName
    END


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

SELECT 'IF NOT EXISTS (SELECT * FROM sysindexes WHERE name = '''
       + 'NCI_' + TableName + '_' + ColumnName + ''') '
       + ' CREATE INDEX [NCI_' + TableName + '_' + ColumnName + '] ON [' + SchemaName + '].[' + TableName + ']([' + ColumnName +'])'
FROM #temp1 
ORDER BY TableName

DROP TABLE #temp1 

SQL 2005 Version

/*
adapted from http://encodo.com/en/blogs.php?entry_id=173
Uses my NCI_tablename-indexname index naming convention
Have added table schemas and 'IF EXISTS' checks to detect if index is already present
*/

SELECT
'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''['
+ IndexSchemas.[name]
+ '].['
+ IndexTables.[name]
+ ']'') AND name = N''IX_'
+ IndexSchemas.[name] + '_' + IndexTables.[name] + '_' + IndexColumns.[name]
+ ''') '
+ 'CREATE NONCLUSTERED INDEX [IX_'
+ IndexSchemas.[name] + '_' + IndexTables.[name] + '_' + IndexColumns.[name]
+ '] ON ['
+ IndexSchemas.[name] + '].[' + IndexTables.[name] 
+ ']( ['
+ IndexColumns.[name]
+ '] ASC ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]'
FROM sys.foreign_keys ForeignKeys
INNER JOIN sys.foreign_key_columns ForeignKeyColumns
  ON ForeignKeys.object_id = ForeignKeyColumns.constraint_object_id
INNER JOIN sys.columns IndexColumns
  ON ForeignKeyColumns.parent_object_id = IndexColumns.object_id
  AND ForeignKeyColumns.parent_column_id = IndexColumns.column_id
INNER JOIN sys.tables IndexTables
  ON ForeignKeyColumns.parent_object_id = IndexTables.object_id
INNER JOIN sys.schemas IndexSchemas
  ON IndexTables.schema_id = IndexSchemas.schema_id
ORDER BY IndexTables.[name], IndexColumns.[name]