Script to Generate Foreign Key Column Indexes

While quality indexing often needs human intervention, not automated tools, there are some good baseline indexes that can be added to a database.

Most of the databases I design lately are star-schema databases with fact tables that have many foreign keys to dimension tables. Out of the gate I typically add basic non-clustered indexes to all of these foreign keys and adjust as necessary once the database begins to get significant use. 

Any easy way to do this is to use the SQL Server system catalog tables to identify all non-indexed foreign key columns and then use that information to generate the CREATE INDEX statements. Below is a query that does just that.

SELECT

  'CREATE INDEX [IX_' + TableName + '_' + ColumnName + '] ON [' + TableName + '] ( [' + ColumnName + '] );'

FROM  (

  SELECT

    o.name     AS TableName

    ,cols.name AS ColumnName

  FROM

    sys.foreign_key_columns fc

    INNER JOIN sys.objects o

      ON fc.parent_object_id = o.object_id

    INNER JOIN sys.columns cols

      ON cols.object_id = o.object_id

         AND fc.parent_column_id = cols.column_id

   

   EXCEPT

   

   SELECT

     o.name AS TableName

     ,cols.name AS ColumnName

   FROM

     sys.index_columns icols

     INNER JOIN sys.objects o

       ON icols.object_Id = o.object_id

     INNER JOIN sys.columns cols

       ON cols.object_id = o.object_id

          AND icols.column_id = cols.column_id

     INNER JOIN sys.schemas s 

ON o.schema_id = s.schema_id

WHERE

s.name <> 'sys'

) t

ORDER  BY

  TableName

  ,ColumnName