Create Sql Server Index on FK's

CreateIndexs.sql

/*
	This will generate a result set where each row is a SQL Statement that will generate an
	index for a specific foreign key. The script first checks for the existance of the index
	it is about to create and if it exists, the index is dropped. The generated script then
	creates the index. The indexes are searched for and created using my own naming guidelines 
	and an example is given of what the script might generate for an index on TableName and FK 
	ColumnName.
	
	--TableName.ColumnName
	--Delete index if it exsits  
	IF EXISTS(SELECT * FROM sys.indexes WHERE object_id= OBJECT_ID('TableName') AND NAME = 'IX_FK_TableName_ColumnName')       
	DROP INDEX [IX_FK_TableName_ColumnName] ON [TableName];  
	GO  

	--Create plain index  
	CREATE INDEX [IX_FK_TableName_ColumnName] ON TableName(ColumnName) 
	GO   
	 --~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  

*/

USE [database]
GO

--Generates default index on foreign keys

SELECT [IndexCreate] =
	'--' + OBJECT_NAME(foreignKeyColumns.parent_object_id) + '.' + COL_NAME(foreignKeyColumns.parent_object_id, foreignKeyColumns.parent_column_id) +  CHAR(13) + CHAR(10) +
	'--Delete index if it exsits' +  CHAR(13) + CHAR(10) +
	'IF EXISTS(SELECT * FROM sys.indexes WHERE object_id= OBJECT_ID(''' + OBJECT_NAME(foreignKeyColumns.parent_object_id) + ''') AND NAME = ''IX_FK_' + OBJECT_NAME(foreignKeyColumns.parent_object_id) + '_'+ COL_NAME(foreignKeyColumns.parent_object_id, foreignKeyColumns.parent_column_id)+ ''')' + CHAR(13) + CHAR(10) +
    '     DROP INDEX [IX_FK_' + OBJECT_NAME(foreignKeyColumns.parent_object_id) + '_'+ COL_NAME(foreignKeyColumns.parent_object_id, foreignKeyColumns.parent_column_id)+ '] ON ['+ OBJECT_NAME(foreignKeyColumns.parent_object_id)  +'];' + CHAR(13) + CHAR(10) + 'GO' +CHAR(13) + CHAR(10) +

	'--Create plain index' +  CHAR(13) + CHAR(10)+
	'CREATE INDEX [IX_FK_' + OBJECT_NAME(foreignKeyColumns.parent_object_id) + '_'+ COL_NAME(foreignKeyColumns.parent_object_id, foreignKeyColumns.parent_column_id)+ '] ON ' + OBJECT_NAME(foreignKeys.parent_object_id) + 
	'(' + COL_NAME(foreignKeyColumns.parent_object_id, foreignKeyColumns.parent_column_id) + ')'+ CHAR(13) + CHAR(10) + ' GO' + CHAR(13) + CHAR(10) +CHAR(13) + CHAR(10) + '--' + REPLICATE('~',150) +CHAR(13) + CHAR(10)  
FROM 
	sys.foreign_keys AS foreignKeys INNER JOIN 
	sys.foreign_key_columns AS foreignKeyColumns ON foreignKeys.OBJECT_ID = foreignKeyColumns.constraint_object_id
ORDER BY [IndexCreate]







--END

If you have index already created on the tables where the index names use some other naming convention other than what is found in the script, running this script will generate duplicate indexs.