SELECT -- which table :
       s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME, 
       -- which index, how many UPDATES
       i.name AS INDEX_NAME, ius.user_updates,
       -- stats on recoverable pages for index
       (SELECT SUM(used_page_count)  
        FROM   sys.dm_db_partition_stats AS ps 
        WHERE  ps.object_id = ius.object_id 
          AND  ius.index_id = ps.index_id) 
          AS INDEX_USED_PAGE,
       -- stats on recoverable pages for the all DB
       SUM((SELECT SUM(reserved_page_count) AS P 
            FROM   sys.dm_db_partition_stats AS ps 
            WHERE  ps.object_id = ius.object_id 
              AND  ius.index_id = ps.index_id)) OVER() 
          AS DB_USED_PAGES,
       -- build the ALTER INDEX SQL command
       N'ALTER INDEX [' + i.name + '] ON [' + s.name 
         + '].[' + o.name + '] DISABLE;' AS SQL_INDEX_DISABLE,
       -- build the ADROP INDEX SQL command
       N'DROP INDEX [' + i.name + '] ON [' 
         + s.name + '].[' + o.name + '];' AS SQL_INDEX_DROP
FROM   sys.dm_db_index_usage_stats AS ius
       JOIN sys.objects AS o 
          ON ius.object_id = o.object_id
       JOIN sys.schemas AS s 
          ON o.schema_id = s.schema_id
       JOIN sys.indexes AS i 
          ON ius.object_id = i.object_id AND 
             ius.index_id = i.index_id
WHERE  ius.index_id > 1             -- ignore tables
  AND  i.is_hypothetical = 0        -- ignore hypothetical indexes
  AND  i.is_primary_key = 0         -- ignore PK
  AND  i.is_unique = 0              -- ignore AK
  AND  i.is_unique_constraint = 0   -- ignore unique indexes
  AND  user_seeks = 0               -- index has no seek
  AND  user_lookups = 0             -- index has no lookup
  AND  user_scans = 0               -- index has no scan
  AND  database_id = DB_ID()        -- only for current DB
  -- ignore empty index (empty table ?)
  AND  EXISTS(SELECT * 
              FROM   sys.dm_db_partition_stats AS ps 
              WHERE  ps.object_id = ius.object_id 
                AND  ius.index_id = ps.index_id 
                AND  row_count > 0) 
  -- ignore the diagnosis if statistics persistence < 31 days
  AND  EXISTS(SELECT *
              FROM   sys.dm_os_sys_info
              WHERE  sqlserver_start_time 
                     <= DATEADD(day, -31, GETDATE()))
ORDER BY 1, 2, 3;