Unused Indexes

select OBJECT_NAME(ix.object_id),
db.name AS DATABASENAME, obj.name as 'Table'
,idx.name as 'Index',
idx.rowcnt, idx.used/128 'MB',
ixu.user_seeks,
ixu.user_scans,
ixu.user_lookups,
ixu.user_updates ,
--OBJECT_NAME(ix.object_id),
substring(ix.type_desc,1,20), 'Unused Stats', is_unique as 'unique', is_primary_key as 'primary',
'IF EXISTS (select *
FROM sys.indexes where name = '''+ix.name+'''
) Drop index ' + ix.name + ' on ' +sc.name+'.'+obj.name
as DropIndexStatement
FROM sys.dm_db_index_usage_stats ixu
join sys.databases db on
db.database_id = ixu.database_id
join sys.objects obj on
ixu.object_id = obj.object_id
join sys.sysindexes idx on
ixu.object_id = idx.id and ixu.index_id = idx.indid
join sys.indexes ix on
ixu.object_id = ix.object_id and ixu.index_id = ix.index_id
join sys.tables tb on
ix.object_id = tb.object_id
join sys.schemas sc on
tb.schema_id = sc.schema_id
where --last_user_seek is null and last_user_scan is null and last_user_lookup is null and
user_scans = 0 and user_seeks = 0 and user_lookups = 0
        and ixu.index_id > 0 and db.database_id = db_id() and obj.type <> 'S'  --and ix.type_desc = 'NONCLUSTERED'
        and ix.is_unique = 0 and ix.is_primary_key = 0  -- don't include unique or primary keys
        and isnull(ixu.user_updates,0) > 0
 and ix.type_desc <> 'HEAP'
        order by OBJECT_NAME(ix.object_id),
        (convert(decimal(19,4),
ISNULL(ixu.user_seeks, 0)) + ISNULL(ixu.user_scans, 0) + ISNULL(ixu.user_lookups, 0))/ISNULL(ixu.user_updates, 0) asc,
user_updates desc, obj.name, ix.name