Find Missing Index Reccomendations

Taken from DotNetVibes - This is derived from this excellent site with a couple very small modifications. I recommend his site.

I have also re-arranged the columns to match SAP DBA cockpit due to the limitations of DBACOCKPIT with SQL Server indexes.

USE master;

SELECT   DISTINCT 
mid.[statement] AS [Database.Schema.Table]
,OBJECT_NAME(mid.[object_id]) AS [Table Name]
,CONVERT(DECIMAL(18, 2) , user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )) AS [index_advantage]
,migs.avg_total_user_cost
,migs.avg_user_impact
,migs.user_seeks
,mid.equality_columns
,mid.inequality_columns
,mid.included_columns
,migs.last_user_seek
,migs.last_user_scan
,migs.unique_compiles
,migs.avg_total_system_cost
,migs.avg_total_user_cost
, (SELECT MAX(p.[rows]) FROM sys.partitions p WITH ( NOLOCK) WHERE [object_id] = mid.[object_id])  AS [Table Rows]
,'CREATE INDEX [Missing_IXNC_' + 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], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' 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] + ')', '')
AS [ProposedIndex]
,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )
INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK ) ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK ) ON mig.index_handle = mid.index_handle
INNER JOIN sys.partitions AS p WITH ( NOLOCK ) ON p.[object_id] = mid.[object_id]
WHERE    mid.database_id = DB_ID()
ORDER BY index_advantage DESC
OPTION ( RECOMPILE );