Finding Unused Indexes on Database

Sometimes the number of indexes on your database may be huge in number and it would be a teadous task to check which are indexes being used and which are not. So here is one of the solution. I found this T-SQL Statement useful which may ease out your task.

Thanks to Search Engines.

USE [YOURDATABASENAME]

GO

SELECT O.NAME AS OBJECT_NAME, I.NAME AS INDEX_NAME,

I.TYPE_DESC, U.USER_SEEKS, U.USER_SCANS,

U.USER_LOOKUPS, U.USER_UPDATES

FROM SYS.INDEXES I

JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID

LEFT JOIN SYS.DM_DB_INDEX_USAGE_STATS U ON I.OBJECT_ID = U.OBJECT_ID

AND I.INDEX_ID = U.INDEX_ID

AND U.DATABASE_ID = DB_ID()

WHERE O.TYPE <> 'S' -- NO SYSTEM TABLES!

ORDER BY (ISNULL(U.USER_SEEKS, 0) + ISNULL(U.USER_SCANS, 0) + ISNULL(U.USER_LOOKUPS, 0) + ISNULL(U.USER_UPDATES, 0)), O.NAME, I.NAME