The data about missing indexes is stored in the following DMV’s which all exclude info about spatial indexes:
sys.dm_db_missing_index_groups
This DMV returns only 2 columns with information about which indexes are in which group.
sys.dm_db_missing_index_group_stats
This DMV returns information about each missing indexes group. It returns info like the estimated average impact or how many seeks, scans and compilations/recompilations would benefit from adding the missing indexes.
sys.dm_db_missing_index_details
This DMV returns detailed information about each missing index like table name that is missing an index and CSV’s of columns that the index would be beneficial on.
sys.dm_db_missing_index_columns
This a Dynamic Management Function (DMF) that accepts an index_handle parameter. It returns columns that should be in the suggested index identified with the index_handle that can be obtained from sys.dm_db_missing_index_details and sys.dm_db_missing_index_groups. It does not include spatial indexes. The column named column_usage returns info on how this column would benefit for a particular index. EQUALITY and INEQUALITY mean that the column would be used in a where clause predicate. INCLUDE means that the column should be an included column on an existing non-clustered index.
OR
Use the follwing script[
^]:
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO
--Amy