Notes

SQL Server script to automatically create indexes for foreign keys

This script will generate the SQL to create an index for every foreign key that is missing one.


Luke Lowrey
Luke Lowrey
DECLARE @IndexName TABLE ([SQL] NVARCHAR(500));

WITH
    v_NonIndexedFKColumns
    AS
    (
                    SELECT
                [Table_Name] = OBJECT_NAME(a.[parent_object_id]),
                [Column_Name] = b.[NAME]
            FROM
                sys.foreign_key_columns a
                        , sys.all_columns b
                        , sys.objects c
            WHERE
                        a.[parent_column_id] = b.[column_id]
                AND a.[parent_object_id] = b.[object_id]
                AND b.[object_id] = c.[object_id]
                AND c.[is_ms_shipped] = 0
        EXCEPT
            SELECT
                [Table_Name] = OBJECT_NAME(a.[object_id]),
                [Column_Name] = b.[NAME]
            FROM
                sys.index_columns a
                        , sys.all_columns b
                        , sys.objects c
            WHERE
                        a.[object_id] = b.[object_id]
                AND a.[key_ordinal] = 1
                AND a.[column_id] = b.[column_id]
                AND a.[object_id] = c.[object_id]
                AND c.[is_ms_shipped] = 0
    )
INSERT INTO @IndexName
    ([SQL])
SELECT
    'CREATE INDEX [IX_' + v.[Table_Name] + '_' + v.[Column_Name] + ']'
    + 'ON [' + SCHEMA_NAME(fk.[schema_id]) + '].[' + v.[Table_Name] + '] ([' + v.[Column_Name] + ']);'
FROM
    v_NonIndexedFKColumns v
                    , sys.all_columns c
                    , sys.all_columns c2
                    , sys.foreign_key_columns fkc
                    , sys.foreign_keys fk
WHERE
                    v.[Table_Name] = OBJECT_NAME(fkc.[parent_object_id])
    AND v.[Column_Name] = c.[NAME]
    AND fkc.[parent_column_id] = c.[column_id]
    AND fkc.[parent_object_id] = c.[object_id]
    AND fkc.[referenced_column_id] = c2.[column_id]
    AND fkc.[referenced_object_id] = c2.[object_id]
    AND fk.[object_id] = fkc.[constraint_object_id]

DECLARE @CurrentSQLCommand NVARCHAR(MAX);
DECLARE sqlCommandsCursor CURSOR FOR
                SELECT [SQL]
FROM @IndexName;
OPEN sqlCommandsCursor;
FETCH NEXT FROM sqlCommandsCursor INTO @CurrentSQLCommand;
WHILE @@FETCH_STATUS = 0
                BEGIN
    -- Un-comment the line below to create the indexes
    -- EXEC sp_executesql @CurrentSQLCommand;
    PRINT @CurrentSQLCommand
    FETCH NEXT FROM sqlCommandsCursor INTO @CurrentSQLCommand;
END
CLOSE sqlCommandsCursor;
DEALLOCATE sqlCommandsCursor;

I have recently run into a couple of SQL server databases with performance issues. Investigation showed the likely issue was they were missing indexes on several foreign keys. A teammate introduced me to the magic script above (originally from MSSQLTips.com) to automatically generate index creation scripts.