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