2005 Unused indexes v2

drop view vw_index_usage
go

create view vw_index_usage as
select    object_name(a.object_id) "table",
        c.name "index",
        (select used/128 from sysindexes b where b.name=c.name and c.index_id = b.indid) "MB",
        (select count(*) from sys.index_columns d where a.object_id = d.object_id and a.index_id = d.index_id and d.is_included_column = 0) "cols",
        (select count(*) from sys.index_columns d where a.object_id = d.object_id and a.index_id = d.index_id and d.is_included_column = 1) "included",
        (a.user_seeks + a.user_scans + a.user_lookups) "hits",
        (a.user_updates) "updates",
        a.last_user_update "stats_date",
        cast(a.user_seeks + a.user_scans + a.user_lookups AS REAL) / cast(case a.user_updates when 0 then 1 else a.user_updates end as REAL) * 100 "ratio",
        'alter index [' + c.name + '] on [' + object_name(a.object_id) + '] disable;' "SQLCmd"
from    sys.dm_db_index_usage_stats a
join sysobjects as o
on        (a.object_id = o.id)
join sys.indexes as c
on        (a.object_id = c.object_id and a.index_id = c.index_id)
where    o.type = 'U'                    -- exclude system tables
and        c.is_unique = 0                    -- no unique indexes
and        c.type = 2                        -- nonclustered indexes only
and        c.is_primary_key = 0            -- no primary keys
and        c.is_unique_constraint = 0        -- no unique constraints
and        c.is_disabled = 0                -- only active indexes
and        a.database_id = DB_ID()            -- for current database only
go