gistfile1.txt

SELECT 
    id.[statement]
    ,id.[equality_columns]
    ,id.[inequality_columns]
    ,id.[included_columns]
    ,gs.[unique_compiles] AS [UniqueCompiles]
    ,gs.[user_seeks] AS [UserSeeks]
    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]
    ,gs.[avg_user_impact] AS [AvgUserImpact]
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
    ,'CREATE NONCLUSTERED INDEX [IX_' + OBJECT_NAME(id.[object_id], id.[database_id]) +
        '_' + format(getdate(),'yyyyMMdd') + 
        '_' + SUBSTRING(CONVERT(varchar(40), NEWID()),0,9) + ']' + 
        ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '')
FROM [sys].[dm_db_missing_index_group_stats] gs
INNER JOIN [sys].[dm_db_missing_index_groups] ig ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db ON db.[database_id] = id.[database_id]
WHERE 1=1
and db.[database_id] = DB_ID()
ORDER BY IndexAdvantage desc