WITH ct AS (SELECT     i.object_id, OBJECT_NAME(i.object_id) AS table_name, i.index_id, SUM(i.user_seeks) AS seeks, SUM(i.user_scans) AS scans, SUM(i.user_lookups)
                                                 AS lookups, sys.schemas.name
                          FROM          sys.tables AS t INNER JOIN
                                                 sys.dm_db_index_usage_stats AS i ON t.object_id = i.object_id INNER JOIN
                                                 sys.schemas ON t.schema_id = sys.schemas.schema_id
                          GROUP BY i.object_id, i.index_id, sys.schemas.name)
    SELECT     'drop index ' + QUOTENAME(stats.name) + '.' + QUOTENAME(stats.table_name) + '.' + i.name AS DropIndexStatement, stats.name AS schemaName,
                            stats.table_name AS TableName, i.name AS IndexName, i.type_desc AS IndexType, stats.seeks + stats.scans + stats.lookups AS TotalAccesses,
                            stats.seeks AS Seeks, stats.scans AS Scans, stats.lookups AS Lookups
     FROM         ct AS stats INNER JOIN
                            sys.indexes AS i ON stats.object_id = i.object_id AND stats.index_id = i.index_id
     WHERE     (stats.seeks + stats.scans + stats.lookups = 0) AND (i.type_desc = 'NONCLUSTERED') AND (i.is_primary_key = 0) AND (i.is_unique = 0) AND
                            (stats.table_name NOT LIKE 'sys%')
     ORDER BY TableName, IndexName