List All Objects In FileGroup with Size

May 7, 2015

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

--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;