Create Missing Index For Foreign Key Reference

[HarryZheng]

--Create Missing Index For Foreign Key Reference
DECLARE @sql nvarchar(max)
SELECT @sql = IsNull(@sql + ';' + char(13) , '') + 'CREATE NONCLUSTERED INDEX [IX_' + tablename + '_' + columnname +'] ON [' + SchemaName + '].[' + tablename + '] ( [' + columnname + '] ASC)'FROM
(
SELECT SCHEMA_NAME(o.schema_id) as SchemaName
,o.name AS TableName
,cols.name AS ColumnName
FROM sys.foreign_key_columns fc
inner join sys.objects o
on fc.parent_object_id = o.object_id
inner join sys.columns cols
on cols.object_id = o.object_id
and fc.parent_column_id = cols.column_id

EXCEPT

SELECT SCHEMA_NAME(o.schema_id)
,o.name
,cols.name
FROM sys.index_columns icols
inner join sys.objects o on icols.object_Id = o.object_id
inner join sys.columns cols on cols.object_id = o.object_id
and icols.column_id = cols.column_id
) T
ORDER BY SchemaName, TableName, ColumnName

Print @sql

exec sp_executesql @sql