List of Table in Filegroup with table size in SQL Server
Sometime back I have provided the script to calculate the size of each schema and table in SQL Server and move index efficiently to different filegroup in different drive but today I come up with little different script. I had requirement to know list of table under each file group along with size of that table and name of clustered index if any.
I have used sys.Allocation_Units, sys.Partitions and sys.indexes system table to find the detail I wanted to know.
As per MSDN:
“sys.Partitions” contains a row for each partition of all the tables and most types of indexes in the database. Special index types like Full-Text, Spatial, and XML are not included in this view. All tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.
“sys.Allocation_Units” contains a row for each allocation unit in database.
“sys.indexes” contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.
So, if we have to get list of filegroup and table name with size, we can utilize sys.partitions and sys.allocation_units. We have had JOIN with sys.indexes as we wanted to know name of clustered index on the table, if available.
Here is the small script I am using to cater my need:
SELECT FILEGROUP_NAME(AU.data_space_id) AS FileGroupName, OBJECT_NAME(Parti.object_id) AS TableName, ind.name AS ClusteredIndexName, AU.total_pages/128 AS TotalTableSizeInMB, AU.used_pages/128 AS UsedSizeInMB, AU.data_pages/128 AS DataSizeInMB FROM sys.allocation_units AS AU INNER JOIN sys.partitions AS Parti ON AU.container_id = CASE WHEN AU.type in(1,3) THEN Parti.hobt_id ELSE Parti.partition_id END LEFT JOIN sys.indexes AS ind ON ind.object_id = Parti.object_id AND ind.index_id = Parti.index_id ORDER BY TotalTableSizeInMB DESC
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.