SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
SELECT
[migs].[avg_user_impact] * ( [migs].[user_seeks] + [migs].[user_scans] ) [avg_estimated_impact],
[user_scans], [user_seeks], [migs].[last_user_seek] [last_user_seek],
OBJECT_NAME([mid].[object_id], [mid].[database_id]) [table_name],
'CREATE INDEX [IX_' + OBJECT_NAME([mid].[object_id], [mid].[database_id]) + '_' +
REPLACE(REPLACE(REPLACE(ISNULL([mid].[equality_columns], ''), ', ', '_'), '[', ''), ']', '') +
CASE WHEN [mid].[equality_columns] IS NOT NULL AND [mid].[inequality_columns] IS NOT NULL THEN '_' ELSE '' END +
REPLACE(REPLACE(REPLACE(ISNULL([mid].[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + ']' +
' ON ' + [mid].[statement] + ' (' + ISNULL([mid].[equality_columns], '') +
CASE WHEN [mid].[equality_columns] IS NOT NULL AND [mid].[inequality_columns] IS NOT NULL THEN ',' ELSE '' END +
ISNULL([mid].[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + [mid].[included_columns] + ')', '') [create_statement]
FROM [sys].[dm_db_missing_index_groups] [mig]
INNER JOIN [sys].[dm_db_missing_index_group_stats] [migs] ON [migs].[group_handle] = [mig].[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] [mid] ON [mig].[index_handle] = [mid].[index_handle]
WHERE [mid].[database_id] = DB_ID()
ORDER BY [avg_estimated_impact] DESC
GO