Unused Indexes – Identifying and Removing

Ive blogged about this previously in the general context of Index Management but ive created a more specific query for unused indexes which lets me quickly identify which indexes are not being used or have never been used in a database. This query goes a step further to find how much space could be saved and creates the dynamic sql to drop the indexes.

    DB_NAME(us.[database_id]) as [DB Name], 
    OBJECT_NAME(us.[object_id]) as [Name],         
    i.name as [Index Name], 
    i.index_id, p.rows as [#Records],
    (a.total_pages * 8) / 1024 as [Reserved(mb)],
    (a.used_pages * 8)/ 1024 as [Used(mb)],
    us.user_seeks, us.user_scans, us.user_lookups, 
    us.last_user_scan, us.last_user_lookup, us.last_user_update,
    'DROP INDEX ' + i.name + ' ON ' + OBJECT_NAME(us.[object_id]) AS [Script]
    sys.dm_db_index_usage_stats us
    inner join sys.indexes AS i 
        ON (us.index_id = i.index_id and us.object_id = i.object_id)
    inner join sys.partitions AS p 
        ON i.object_id = p.object_id and i.index_id = p.index_id 
    inner join sys.allocation_units AS a 
        ON p.partition_id = a.container_id
    us.database_id = dB_id('MyDB')
    and (us.last_user_seek is NULL 
        and us.last_user_scan is NULL 
        and us.last_user_lookup is NULL)
    and us.index_id >  1        -- no primary keys
    OBJECT_NAME(us.[object_id]), i.index_id