Get Missing Indexes from database on SQL Server

Get Missing indexes from a Database in SQL Server:


Create Proc p_getMissingIndexes
(@Databasename varchar(50)='GBRView')

as

--Declare @cmdstr as varchar(100)
--SELECT @cmdstr = 'use ' + @Databasename
--EXEC(@cmdstr)


SELECT --DB.name [Database],
D.statement
+ ' (' + ISNULL (D.equality_columns,'')
+ CASE WHEN D.equality_columns IS NOT NULL AND D.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (D.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + D.included_columns + ')', '') AS [Index Detail],

column_name, unique_compiles, avg_user_impact, last_user_seek, user_seeks, column_usage
FROM sys.dm_db_missing_index_groups G
JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle
JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle
Inner join master.dbo.sysdatabases(nolock) DB on D.Database_id = DB.[DBID]
CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC
where lower(DB.name) = lower(@Databasename)
ORDER BY D.index_handle, [statement];