Credit: Basit’s SQL Server Tips

The following is very handy to check for any unused index’s on a MSSQL database, this should only be run once the database has been up for at least a week or when all scheculed jobs have been allowed to run at least once. The only change I made is to display the table and index name.

SELECT o.name,i.name,u.*
FROM [sys].[indexes] i
INNER 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() --returning the database ID of the current database
WHERE o.[type] <> 'S' --shouldn't be a system base table
    AND i.[type_desc] <> 'HEAP'
    AND i.[name] NOT LIKE 'PK_%'
    AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0
    AND u.[last_system_scan] IS NOT NULL
ORDER BY 1 ASC