List All Objects In FileGroup with Size

Recently at my work due low free space on a LUN I was forced to rebuild few indexes on a different filegroup (which resides on a different LUN). I wanted to list all user objects belong to a single filegroup including size. Did not find a query online with everything I needed. So I wrote this one and wanted to share.

--Run this in the context of database that you are working on
SELECT
    FileGroup = FILEGROUP_NAME(a.data_space_id) ,
    TableName = OBJECT_NAME(p.object_id) ,
    IndexName = i.name ,
    8 * SUM(a.used_pages) AS 'Size(KB)' ,
    8 * SUM(a.used_pages) / 1024 AS 'Size(MB)' ,
    8 * SUM(a.used_pages) / 1024 / 1024 AS 'Size(GB)'
FROM
    sys.allocation_units a
INNER JOIN sys.partitions p
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
LEFT JOIN sys.indexes i
ON  i.object_id = p.object_id
    AND i.index_id = p.index_id
--Uncomment if you want to see indexes for one filegroup
--WHERE
--    a.data_space_id = 1
GROUP BY
    a.data_space_id ,
    p.object_id ,
    i.object_id ,
    i.index_id ,
    i.name
ORDER BY
    FILEGROUP_NAME(a.data_space_id) ,
    SUM(a.used_pages) DESC;
FacebookTwitterGoogle+Share