Script to find unused indexes in all databases, for tables that are old (more than 30 days old) and big (more than 200k rows)

Find Unused Indexes.sql

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp;
CREATE TABLE #tmp (DBName SYSNAME, SchemaName SYSNAME, TableName SYSNAME, IndexName SYSNAME NULL, RowsCount INT, IndexSizeKB INT, UpdatesCount INT NULL, DropCMD NVARCHAR(MAX), TableCreatedDate DATETIME NULL, LastStatsDate DATETIME);

 INSERT INTO #tmp(DBName, SchemaName, TableName, IndexName, RowsCount, IndexSizeKB, DropCMD, LastStatsDate, TableCreatedDate, UpdatesCount)
 EXEC sp_MSforeachdb N'
 IF EXISTS (SELECT * FROM sys.databases WHERE database_id > 4 AND name = ''?'' AND state_desc = ''ONLINE'' AND DATABASEPROPERTYEX([name], ''Updateability'') = ''READ_WRITE'')
 BEGIN
 USE [?];
 PRINT DB_NAME();
 SELECT
 db_name() AS DBNAme,
 OBJECT_SCHEMA_NAME(indexes.object_id) as SchemaName,
 OBJECT_NAME(indexes.object_id) AS Table_name,
 indexes.name AS Index_name,
 SUM(partitions.rows),
 SUM(partition_stats.reserved_page_count) * 8,
 ''DROP INDEX ''+QUOTENAME(indexes.name)+'' ON ''+QUOTENAME(db_name())+''.''+ QUOTENAME(OBJECT_SCHEMA_NAME(indexes.object_id))+''.''+QUOTENAME(OBJECT_NAME(indexes.object_id)) as dropcmd ,
 STATS_DATE(indexes.object_id, indexes.index_id) StatsDate,
 tables.create_date,
 ISNULL(usage_stats.user_updates, 0) + ISNULL(usage_stats.system_updates, 0)
 FROM
 sys.indexes
 INNER JOIN sys.tables
 ON indexes.object_id = tables.object_id 
 AND tables.create_date < DATEADD(dd, -30, GETDATE())
 AND tables.is_ms_shipped = 0
 AND indexes.index_id > 1
 AND indexes.is_primary_key = 0
 AND indexes.is_unique = 0
 AND indexes.is_disabled = 0
 AND indexes.is_hypothetical = 0
 INNER JOIN sys.partitions
 ON indexes.object_id = partitions.object_id
 AND indexes.index_id = partitions.index_id
 LEFT JOIN sys.dm_db_index_usage_stats AS usage_stats
 ON indexes.index_id = usage_stats.index_id AND usage_stats.OBJECT_ID = indexes.OBJECT_ID
 LEFT JOIN sys.dm_db_partition_stats AS partition_stats
 ON indexes.index_id = partition_stats.index_id AND partition_stats.OBJECT_ID = indexes.OBJECT_ID
 WHERE
 usage_stats.index_id IS NULL
 OR ( ISNULL(usage_stats.system_seeks,0) = 0
 AND ISNULL(usage_stats.user_seeks,0) = 0
 AND ISNULL(usage_stats.user_scans,0) = 0
 )
 GROUP BY
 indexes.object_id,
 tables.create_date,
 indexes.index_id,
 indexes.name,
 usage_stats.user_seeks,
 usage_stats.user_scans,
 usage_stats.user_updates,
 usage_stats.system_updates
 HAVING
 SUM(partitions.rows) > 200000
 END'

SELECT *
FROM #tmp
ORDER BY DBName ASC, IndexSizeKB DESC, RowsCount DESC