List All Objects In FileGroup with Size
May 7, 2015
Recently at my work due to 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. I 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;