Generating Indexes for Foreign Keys

Surprisingly SQL Server doesn’t automatically create indexes just because you have a foreign key. So at the end of a development project I just used the script below to generate the SQL needed.

select 'create index indx_'+t.name +'_' + c.name +
     ' on ' + t.name +'(' + c.name + ');' 
from sys.foreign_key_columns as fk
inner join sys.tables as t 
on fk.parent_object_id = t.object_id
inner join sys.columns as c 
on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id
where not exists (select 1 
    from sys.index_columns ic
    where ic.object_id = t.object_id
    and ic.column_id = c.column_id)
order by 1

This returns the commands that I can then run to create the indexes.

create index indx_ActivityLog_activityTypeId on ActivityLog(activityTypeId);

create index indx_ActivityLog_userId on ActivityLog(userId);

create index indx_Areas_managerId on Areas(managerId);

create index indx_Categories_areaId on Categories(areaId);

create index indx_CriteriaItems_criteriaColorId on CriteriaItems(criteriaColorId);