CreateFKIndexes.sql

CreateFKIndexes.sql

/*
ORIGINAL SCRIPT: https://lukelowrey.com/sql-server-script-to-automatically-create-indexes-for-foreign-keys/
TDC: script modifications
    - include the schema name when creating the index
    - switched to inner join syntax
    - switched to using concat for string concatenation
    - general cleanup
*/

DECLARE @IndexName TABLE (
    [SQL] NVARCHAR(2000)
);

SET NOCOUNT ON

;WITH [v_NonIndexedFKColumns] AS (
    SELECT OBJECT_NAME([a].[parent_object_id]) AS [Table_Name],
           [b].[name] AS [Column_Name]
    FROM [sys].[foreign_key_columns] AS [a]
    INNER JOIN [sys].[all_columns] AS [b] 
        ON [a].[parent_column_id] = [b].[column_id]
            AND [a].[parent_object_id] = [b].[object_id]
    INNER JOIN [sys].[objects] AS [c] 
        ON [b].[object_id] = [c].[object_id]
    WHERE [c].[is_ms_shipped] = 0
    
    EXCEPT
    
    SELECT OBJECT_NAME([a].[object_id]) AS [Table_Name],
           [b].[name] AS [Column_Name]
    FROM [sys].[index_columns] AS [a]
    INNER JOIN [sys].[all_columns] AS [b] 
        ON [a].[object_id] = [b].[object_id]
            AND [a].[column_id] = [b].[column_id]
    INNER JOIN [sys].[objects] AS [c] 
        ON [a].[object_id] = [c].[object_id]
    WHERE [a].[key_ordinal] = 1
      AND [c].[is_ms_shipped] = 0
)

INSERT INTO @IndexName 
    ([SQL])
SELECT CONCAT('CREATE INDEX [', SCHEMA_NAME([fk].[schema_id]), '].[IX_', [v].[Table_Name], '_', [v].[Column_Name],']
    ON [', SCHEMA_NAME([fk].[schema_id]), '].[', [v].[Table_Name], '] ([',  [v].[Column_Name] + ']);')
FROM [v_NonIndexedFKColumns] AS [v]
INNER JOIN [sys].[all_columns] AS [c] 
    ON [v].[Column_Name] = [c].[name]
INNER JOIN [sys].[foreign_key_columns] AS [fkc] 
    ON [v].[Table_Name] = OBJECT_NAME([fkc].[parent_object_id])
        AND [fkc].[parent_column_id] = [c].[column_id]
        AND [fkc].[parent_object_id] = [c].[object_id]
INNER JOIN [sys].[all_columns] AS [c2] 
    ON [fkc].[referenced_column_id] = [c2].[column_id]
        AND [fkc].[referenced_object_id] = [c2].[object_id]
INNER JOIN [sys].[foreign_keys] AS [fk] 
    ON [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];