Quick Query to Display Database by Dataspace, Table, and Index

This query just breaks down the current database by dataspace, table, and index, giving you some useful information such as size in MB, pathname of the dataspace file, etc.

-- Shows size of each object in each dataspace in the current database. 
  select db_name()                              as DatabaseName
       , ds.name                                as DataSpaceName
       , ds.type_desc                           as DataSpaceTypeDesc
       , o.name                                 as TableName
       , i.name                                 as IndexName
       , i.type_desc                            as IndexDesc
       , (sum(ps.used_page_count) * 8) / 1024   as IndexSizeMB
       , df.physical_name                       as PathName
    from sys.objects                 o
    join sys.indexes                 i 
      on o.object_id = i.object_id 
    join sys.data_spaces             ds 
      on ds.data_space_id = i.data_space_id
    join sys.database_files          df
      on ds.data_space_id = df.data_space_id
    join sys.dm_db_partition_stats   ps
      on i.object_id = ps.object_id
group by ds.name
       , o.name
       , i.name
       , ds.type_desc
       , i.type_desc
       , df.physical_name
order by ds.name
       , o.name
       , i.name