Wednesday, May 16, 2012

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.

  'CREATE INDEX [IX_' + TableName + '_' + ColumnName + '] ON [' + TableName + '] ( [' + ColumnName + '] );'
  SELECT     AS TableName
    , AS ColumnName
    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
   SELECT AS TableName
     , AS ColumnName
     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 <> 'sys'
) t

No comments:

Post a Comment