Find unused indexes

USE AdventureWorks

GO

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