Generate index on foreign key columns

-- declare memory table

DECLARE @INDEX_TABLE TABLE(
primary_key INT IDENTITY(1,1) NOT NULL, 
schema_name NVARCHAR(100), 
table_name NVARCHAR(100), 
column_name NVARCHAR(100),
new_index_name NVARCHAR(100)
)


-- feed memory table by all foreign key without index in database
INSERT INTO @INDEX_TABLE
SELECT
S.name as [Schema name],
object_name(T.object_id) AS [Table name],
C.name AS [Column name],
''
FROM 
sys.columns C
LEFT JOIN sys.tables T ON (C.object_id = T.object_id)
LEFT JOIN sys.schemas S ON (S.schema_id = T.schema_id)
LEFT JOIN sys.foreign_key_columns FKC ON (FKC.parent_object_id = C.object_id AND C.column_id = FKC.parent_column_id)
LEFT JOIN sys.foreign_keys FK ON (FKC.constraint_object_id = FK.object_id)

LEFT JOIN sys.index_columns IC ON (IC.object_id = C.object_id AND IC.column_id = C.column_id)
LEFT JOIN sys.indexes I ON (I.index_id = IC.index_id  AND I.object_id = C.object_id)
WHERE 
T.object_id is not null
AND FK.name IS NOT NULL
AND I.name IS NULL
ORDER BY 
S.name,
object_name(T.object_id),
C.name 



DECLARE @loop_counter INT
DECLARE @item_counter INT

SET @loop_counter = ISNULL((SELECT COUNT(*) FROM @INDEX_TABLE),0)
SET @item_counter = 1

DECLARE @schema_name VARCHAR(100)
DECLARE @table_name VARCHAR(100)
DECLARE @column_name VARCHAR(100)
DECLARE @query NVARCHAR(1000)
DECLARE @index_name VARCHAR(200)

WHILE @loop_counter > 0 AND @item_counter <= @loop_counter
BEGIN

-- get one row from memory table
SELECT 
@schema_name = schema_name, 
@table_name = table_name,
@column_name = column_name
FROM 
@INDEX_TABLE
WHERE 
primary_key = @item_counter


-- prepare query
SET @index_name = 'IX_' + @table_name + '_' + @column_name
SET @query = 'CREATE NONCLUSTERED INDEX [' + @index_name + '] ON ['+ @schema_name+ '].[' + @table_name + '] 
(
[' + @column_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]
'

EXEC sp_executesql @query

UPDATE @INDEX_TABLE 
SET 
new_index_name = @index_name
WHERE 
primary_key = @item_counter

SET @item_counter =  @item_counter + 1
END

-- present all FKey's with new index
SELECT 
schema_name, 
table_name, 
column_name,
new_index_name
FROM 
@INDEX_TABLE