Skip to content

Add missing indexes on foreign key columns

Add indexes quick and easy

Inspired by the entry “Unindexed Foreign Keys” from Brent’s Weekly Links, I extended the provided script with ready to deploy sql code. Have fun!


;WITH fk_cte AS 
( 
    SELECT   
    SCHEMA_NAME(soPkTable.schema_id) PK_TABLE_SCHEMA_NAME,
    OBJECT_NAME(fk.referenced_object_id) PK_TABLE,
    c2.name PK_COLUMN,
    kc.name PK_INDEX_NAME,
    SCHEMA_NAME(soFkTable.schema_id) FK_TABLE_SCHEMA_NAME,
    OBJECT_NAME(fk.parent_object_id) FK_TABLE,
    c.name FK_COLUMN,
    fk.name FK_NAME,
    fk.parent_object_id
    FROM sys.foreign_keys fk
    INNER JOIN sys.foreign_key_columns fkc 
        ON fkc.constraint_object_id = fk.object_id
    INNER JOIN sys.objects soPkTable
        ON soPkTable.object_id = fk.referenced_object_id
    INNER JOIN sys.objects soFkTable
        ON soFkTable.object_id = fk.parent_object_id
    INNER JOIN sys.columns c 
        ON c.object_id = fk.parent_object_id 
        AND c.column_id = fkc.parent_column_id
    LEFT JOIN sys.columns c2 
        ON c2.object_id = fk.referenced_object_id 
        AND c2.column_id = fkc.referenced_column_id
    LEFT JOIN sys.key_constraints kc 
        ON kc.parent_object_id = fk.referenced_object_id 
        AND kc.type = 'PK'
    LEFT JOIN sys.index_columns ic 
        ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    LEFT JOIN sys.indexes i 
        ON i.object_id = ic.object_id 
        AND i.index_id = ic.index_id
    WHERE
        i.object_id IS NULL
)
SELECT  
 cte.*
,'CREATE NONCLUSTERED INDEX [IX_'+cte.FK_TABLE_SCHEMA_NAME+'_'+cte.FK_TABLE+'_'+cte.FK_COLUMN+'] ON ['+cte.FK_TABLE_SCHEMA_NAME+'].['+cte.FK_TABLE+'] (['+cte.FK_COLUMN+'])' sqlForeignKey
FROM fk_cte cte
LEFT JOIN sys.dm_db_partition_stats ps 
    on ps.object_id = cte.parent_object_id 
    and ps.index_id <= 1
ORDER BY used_page_count desc

    


Published inUncategorized

Comments are closed.

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close