List missing indexes since the last restart (SQL Server 2005+)

gistfile1.sql

/* ------------------------------------------------------------------
-- Title:	FindMissingIndexes
-- Author:	Brent Ozar
-- Date:	2009-04-01 
-- Modified By: Clayton Kramer <ckramer.kramer @="" gmail.com="">
-- Description: This query returns indexes that SQL Server 2005 
-- (and higher) thinks are missing since the last restart. The 
-- "Impact" column is relative to the time of last restart and how 
-- bad SQL Server needs the index. 10 million+ is high.
-- Changes: Updated to expose full table name. This makes it easier
-- to identify which database needs an index. Modified the 
-- CreateIndexStatement to use the full table path and include the
-- equality/inequality columns for easier identifcation.
------------------------------------------------------------------ */

SELECT  
	[Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),  
	[Table] = [statement],
	[CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX '
		+ LEFT(
			'IX_' 
			+ sys.objects.name COLLATE DATABASE_DEFAULT
			+ CASE WHEN mid.equality_columns IS NOT NULL
				THEN '_' + REPLACE(REPLACE(REPLACE(mid.equality_columns, '[', ''), ']',''), ', ','_') 
				ELSE ''
			  END
			+ CASE WHEN mid.inequality_columns IS NOT NULL
				THEN '_' + REPLACE(REPLACE(REPLACE(mid.inequality_columns, '[', ''), ']',''), ', ','_') 
				ELSE ''
			  END
			+ CASE WHEN mid.included_columns IS NOT NULL
				THEN '_INC_' + REPLACE(REPLACE(REPLACE(mid.included_columns, '[', ''), ']',''), ', ','_') 
				ELSE ''
			  END
		, 128)
		+ ' ON ' 
		+ [statement] 
		+ ' ( ' + IsNull(mid.equality_columns, '') 
		+ CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE 
			CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END 
		+ mid.inequality_columns END + ' ) ' 
		+ CASE WHEN mid.included_columns IS NULL THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END 
		+ ';', 
	mid.equality_columns,
	mid.inequality_columns,
	mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs 
	INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
	INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle 
	INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID 
WHERE (migs.group_handle IN 
		(SELECT TOP (500) group_handle 
		FROM sys.dm_db_missing_index_group_stats WITH (nolock) 
		ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))  
	AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1 
ORDER BY [Impact] DESC , [CreateIndexStatement] DESC

Source: http://sqlserverpedia.com/wiki/Find_Missing_Indexes#SQLServerPedia_Fan_Contribution