List of Table in Filegroup with table size in SQL Server

List of Table in Filegroup with table size in SQL Server

Size

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

 

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

One thought on “List of Table in Filegroup with table size in SQL Server

Comments are closed.