Managing indexes for foreign keys

Having an index for every foreign key is usually a good thing. If you need to figure out if you have any this script might help you out. It finds all existing indexes for foreign keys and creates drop statements for them. It also creates index create statements for all foreign keys including the ones that already existed. So, by dropping all existing and using the create statements you get consistently named indexes for all foreign keys in all your tables.

select 
[TABLE] = tbl.name
,[SCHEMA_ID] = sch.name
,[INDEX] = idx.name
,[IDX_NAME] = 'IX_' +  col.name
,[COLUMN] = col.name
,[FK] = fk.name
,[CREATE_SQL] = 'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + sch.name + '.['
                + tbl.[name]
                + ']'') AND name = N''IX_'
                + col.[name]
                + ''') '
                + 'CREATE NONCLUSTERED INDEX [IX_'
                + col.[name]
                + '] ON '  + sch.name + '.['
                + tbl.[name]
                + ']( ['
                + col.[name]
                + '] ASC ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]'
,[DROP_SQL] = CASE
                WHEN idx.name IS NOT NULL
                THEN 
                    'DROP INDEX [' + idx.name + '] ON ' + sch.name + '.[' + tbl.name + ']'     
                ELSE
                    ''
              END
from sys.tables as tbl
inner join sys.foreign_keys as fk on tbl.object_id = fk.parent_object_id
inner join sys.columns as col on tbl.object_id = col.object_id
inner join sys.foreign_key_columns as fkcol on fk.object_id = fkcol.constraint_object_id and col.column_id = fkcol.parent_column_id
inner Join sys.schemas sch on fk.schema_id = sch.schema_id
left outer join (
       select idx.name, idx.object_id, idxcol.column_id, idx.is_primary_key
       from sys.indexes as idx
       join sys.index_columns as idxcol on idx.index_id = idxcol.index_id and idx.object_id = idxcol.object_id
       where idx.is_primary_key = 0
) as idx
on tbl.object_id = idx.object_id and fkcol.parent_column_id = idx.column_id
order by tbl.name
go