• I'm thinking there's something wrong in this script... when I run it against one of my development databases on a table with 6 indexes, it only shows 2 of them,  because of the join to sys.dm_db_index_usage_stats should be LEFT OUTER JOIN.  Also, it returns 2 copies of each of the 2 indexes it does return, so there's likely another bad join somewhere.

    I'm thinking to truly do analysis like this, it would be best to see all the indexes on the table, even if they are not currently in the usage stats.  I'd be wary of dropping an index without doing repeated analysis over time in case an index is used by a weekly or monthly process not currently reflected.  Here's a query I use to look at index usage:

    SELECT s.name + N'.' + t.name AS table_name, i.name AS index_name, i.type_desc, i.is_unique,
        SubString(
           (SELECT N', ' + c.name + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N'' END
               FROM sys.index_columns ic INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
              WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
              ORDER BY ic.key_ordinal FOR XML PATH('')), 3, 1000) AS columns,
        SubString(
           (SELECT N', ' + c.name
               FROM sys.index_columns ic INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
              WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
              ORDER BY ic.index_column_id FOR XML PATH('')), 3, 1000) AS included,
        i.filter_definition, ps.size_MB, d.name AS FileGroup, iu.*
      FROM sys.indexes i
        INNER JOIN sys.tables t ON i.object_id = t.object_id
        INNER JOIN sys.schemas s on t.schema_id = s.schema_id
        INNER JOIN sys.data_spaces d on i.data_space_id = d.data_space_id
        LEFT OUTER JOIN sys.dm_db_index_usage_stats iu ON i.object_id = iu.object_id AND i.index_id = iu.index_id AND iu.database_id = DB_ID()
        LEFT OUTER JOIN (SELECT object_id, index_id, SUM(used_page_count) / 128 AS size_MB FROM sys.dm_db_partition_stats GROUP BY object_id, index_id) ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
      WHERE i.name IS NOT NULL
        AND i.object_id > 100
        AND t.name in ('myTableName')
      ORDER BY s.name, t.name, i.name