which includes the filegroup and location.
Was reading an old post of mine, and wanted to revisit it and add the ability to check the sizes of unused indexes. Pretty easy.
Updated this from a subquery to an inner join, which gives you the usage stats as well.
select -- i.[object_id], -- i.index_id, o.name as Table_Name, i.NAME AS Index_Name, i.type_desc, -- p.partition_number, p.rows as [#Records], a.total_pages * 8 as [Reserved(kb)], a.used_pages * 8 as [Used(kb)], s.user_seeks, s.user_scans, s.user_lookups from sys.indexes as i inner join sys.partitions as p on i.object_id = p.object_id and i.index_id = p.index_id inner join SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID INNER JOIN sys.allocation_units AS a on (a.type = 2 AND p.partition_id = a.container_id) OR ((a.type = 1 OR a.type = 3) AND p.hobt_id = a.container_id) INNER JOIN SYS.DM_DB_INDEX_USAGE_STATS S ON S.OBJECT_ID = I.OBJECT_ID AND I.INDEX_ID = S.INDEX_ID AND DATABASE_ID = DB_ID(DB_NAME()) and o.type_desc NOT IN ('SYSTEM_TABLE', 'INTERNAL_TABLE') -- No system tables! AND (ISNULL(s.user_seeks, 0) + ISNULL(s.user_scans, 0) + ISNULL(s.user_lookups, 0)) < 100 WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1 AND i.TYPE_DESC <> 'HEAP' and i.type <> 1 -- clustered index order by o.NAME, i.name GO