I’ve used SQL script similar to paul_nielsen’s to Create Indexes for Foreign Keys and added “if not exists” condition
DECLARE @SQL VARCHAR(max); SET @SQL = ''
SELECT @SQL = @SQL +
'if not exists (select * from sys.indexes
where id=object_id(''' + TableName +''') and name=''Ix' + ForeignKeyName+''')
CREATE INDEX Ix' + ForeignKeyName
+ ' ON ' + TableName + '(' + ColumnName + ');
At the end I would recommend to print @SQL to show all new lines correctl in messages ta of SSMS,
rather than SELECT @SQL or execute SQL
Another script can be found here(login is required): http://www.sqlservercentral.com/scripts/Index+Management/62069/
Creating an index on a foreign key is often useful for the following reasons:
Indexing FOREIGN KEY Constrains
- Changes to PRIMARY KEY constraints are checked with FOREIGN KEY constraints in related tables.
- Foreign key columns are frequently used in join criteria when the data from related tables is combined in queries by matching the column or columns in the FOREIGN KEY constraint of one table with the primary or unique key column or columns in the other table. An index enables the Database Engine to quickly find related data in the foreign key table.