Geeks With Blogs

News My Blog has been MOVED to
Michael Freidgeim's OLD Blog My Blog has been MOVED to

I’ve used SQL script similar to paul_nielsen’s to Create Indexes for Foreign Keys and added “if not exists” condition

'if not exists (select * from sys.indexes
 where id=object_id(''' + TableName +''') and name=''Ix' + ForeignKeyName+''')
 CREATE INDEX Ix' + ForeignKeyName
    + ' ON ' + TableName + '(' + ColumnName + ');

print @SQL

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):

Indexing FOREIGN KEY Constrains

Creating an index on a foreign key is often useful for the following reasons:
  • 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.
Posted on Sunday, June 5, 2011 8:42 AM SQL Server | Back to top

Comments on this post: SQL Script to create indexes for Foreign keys

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Michael Freidgeim | Powered by: