MS SQL: Creates or updates a procedure which creates SQL script to create indexes for foreign keys which does not have an index.

CreateIndexesForForeignKeysWithMissingIndex.sql

-- MS SQL, SQL Azure
CREATE OR ALTER PROCEDURE CreateIndexesForForeignKeysWithMissingIndex
AS
BEGIN
    WITH FK_Indexes AS (
        SELECT 
            fk.name AS ForeignKey,
            tp.name AS TableName,
            fk.parent_object_id AS TableObjectID,
            fkc.parent_column_id AS ColumnID,
            cp.name AS FK_ColumnName,
            ic.index_id
        FROM 
            sys.foreign_keys fk
            INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
            INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id
            INNER JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
            LEFT JOIN sys.index_columns ic ON ic.object_id = fkc.parent_object_id AND ic.column_id = fkc.parent_column_id
    )
    , ForeignKeyColumns AS (
        SELECT 
            fk.TableObjectID,
            fk.ForeignKey,
            STRING_AGG(fk.FK_ColumnName, ', ') WITHIN GROUP (ORDER BY fk.ColumnID) AS ForeignKeyColumns
        FROM 
            FK_Indexes fk
        GROUP BY 
            fk.TableObjectID, fk.ForeignKey
    )
    , IndexedColumns AS (
        SELECT 
            ic.object_id,
            ic.index_id,
            STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.index_column_id) AS IndexColumns
        FROM 
            sys.index_columns ic
            INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        GROUP BY 
            ic.object_id, ic.index_id
    )
    SELECT 
        fk.TableName,
        fk.ForeignKey,
        fkcols.ForeignKeyColumns,
        ISNULL(i.name, '-') AS IndexName,
        ISNULL(ic.IndexColumns, '-') AS IndexColumns
    FROM 
        FK_Indexes fk
        LEFT JOIN sys.indexes i ON fk.TableObjectID = i.object_id AND fk.index_id = i.index_id
        LEFT JOIN IndexedColumns ic ON fk.TableObjectID = ic.object_id AND fk.index_id = ic.index_id
        LEFT JOIN ForeignKeyColumns fkcols ON fk.TableObjectID = fkcols.TableObjectID AND fk.ForeignKey = fkcols.ForeignKey
    ORDER BY 
        fk.TableName, fk.ForeignKey;

    WITH FK_Indexes AS (
        SELECT 
            fk.name AS ForeignKey,
            tp.name AS TableName,
            fk.parent_object_id AS TableObjectID,
            fkc.parent_column_id AS ColumnID,
            cp.name AS FK_ColumnName,
            ic.index_id
        FROM 
            sys.foreign_keys fk
            INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
            INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id
            INNER JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
            LEFT JOIN sys.index_columns ic ON ic.object_id = fkc.parent_object_id AND ic.column_id = fkc.parent_column_id
    )
    , ForeignKeyColumns AS (
        SELECT 
            fk.TableObjectID,
            fk.TableName,
            fk.ForeignKey,
            STRING_AGG(fk.FK_ColumnName, ', ') WITHIN GROUP (ORDER BY fk.ColumnID) AS ForeignKeyColumns
        FROM 
            FK_Indexes fk
        GROUP BY 
            fk.TableObjectID, fk.TableName, fk.ForeignKey
    )
    , IndexedColumns AS (
        SELECT 
            ic.object_id,
            ic.index_id,
            STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.index_column_id) AS IndexColumns
        FROM 
            sys.index_columns ic
            INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        GROUP BY 
            ic.object_id, ic.index_id
    )
    SELECT DISTINCT
        'CREATE NONCLUSTERED INDEX IX_' + fk.TableName + '_' + REPLACE(fkcols.ForeignKeyColumns, ', ', '_') + ' ON ' + '[' + fk.TableName + ']' + ' (' + fkcols.ForeignKeyColumns + ');' AS CreateIndexStatement
    FROM 
        FK_Indexes fk
        LEFT JOIN sys.indexes i ON fk.TableObjectID = i.object_id AND fk.index_id = i.index_id
        LEFT JOIN IndexedColumns ic ON fk.TableObjectID = ic.object_id AND fk.index_id = ic.index_id
        LEFT JOIN ForeignKeyColumns fkcols ON fk.TableObjectID = fkcols.TableObjectID AND fk.ForeignKey = fkcols.ForeignKey
    WHERE 
        fk.index_id IS NULL
    ORDER BY 
        CreateIndexStatement;
END;