Foreign Keys without Index

Run below script to generate a create-script for inserting indexes – over all tables in the database.


SELECT  
'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].['  
+ IndexTables.[name]  
+ ']'') AND name = N''NCI_'  
+ IndexTables.[name] + '_' + IndexColumns.[name]  
+ ''') '  
+ 'CREATE NONCLUSTERED INDEX [NCI_'  
+ IndexTables.[name] + '_' + IndexColumns.[name]  
+ '] ON [dbo].['  
+ IndexTables.[name]  
+ ']( ['  
+ IndexColumns.[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]'  
FROM sys.foreign_keys ForeignKeys  
INNER JOIN sys.foreign_key_columns ForeignKeyColumns  
  ON ForeignKeys.object_id = ForeignKeyColumns.constraint_object_id  
INNER JOIN sys.columns IndexColumns  
  ON ForeignKeyColumns.parent_object_id = IndexColumns.object_id  
  AND ForeignKeyColumns.parent_column_id = IndexColumns.column_id  
INNER JOIN sys.tables IndexTables  
  ON ForeignKeyColumns.parent_object_id = IndexTables.object_id  
ORDER BY IndexTables.[name], IndexColumns.[name]