find Unused indexes in SQL Server 2005

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
 
 
This is only works for SQL Server 2005 only