I'm just going to decorate this post with a much more upbeat photo of Denise Milani in a captivating, top-heavy series of photographs in a Southwest desert setting. 

Thinking of how big things are, I recently ran into an issue of an index file group growing pretty large, and I wanted to know which indexes were in it, and how big they were.  My biggest concern was non-clustered indexes that were changed to clustered indexes (causing the whole table to move into the index file group).

I have previously found two great scripts, one that shows the size of an index and another that shows what is in a file group.  I combined these two together to get a complete picture of what I needed.

Script for Indexes Size and What File Group they are in:

Select FileGroup = FILEGROUP_NAME(a.data_space_id),index_listing.* from (
Select
    s.[name] as [schema],
    Case
  When i.[name] is null
  Then o.[name]
  Else i.[name]
 end as name,
    i.type_desc,
    space_used_in_mb = (page_count * 8.0 / 1024.0),
    space_used_in_kb = (page_count * 8.0),
 i.object_id,i.index_id
From
    sys.indexes I
Inner Join
    sys.dm_db_index_physical_stats(db_id(), object_id('.'), null, null, null) 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.schemas s
  on o.[schema_id] = s.[schema_id]
) index_listing
INNER JOIN sys.partitions p on index_listing.object_id = p.object_id
 AND index_listing.index_id = p.index_id
Left Join sys.allocation_units a ON a.container_id = CASE WHEN a.type in(1,3)
 THEN p.hobt_id ELSE p.partition_id END AND p.object_id > 1024
--where FILEGROUP_NAME(a.data_space_id) = 'Index_data'
-- and index_listing.type_desc = 'Clustered'
Order by space_used_in_mb Desc