Missing Index Script

Missing Index Script

SELECT TOP 25
  
 dm_mid.database_id AS DatabaseID,
  
 dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
  
 dm_migs.last_user_seek AS Last_User_Seek,
  
 OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
  
 'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
  
 + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
  
 CASE
  
 WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
  
 ELSE ''
  
 END
  
 + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
  
 + ']'
  
 + ' ON ' + dm_mid.statement
  
 + ' (' + ISNULL (dm_mid.equality_columns,'')
  
 + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
  
 '' END
  
 + ISNULL (dm_mid.inequality_columns, '')
  
 + ')'
  
 + ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
  
 FROM sys.dm_db_missing_index_groups dm_mig
  
 INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
  
 ON dm_migs.group_handle = dm_mig.index_group_handle
  
 INNER JOIN sys.dm_db_missing_index_details dm_mid
  
 ON dm_mig.index_handle = dm_mid.index_handle
  
 WHERE dm_mid.database_ID = DB_ID()
  
 ORDER BY Avg_Estimated_Impact DESC
  
 GO