BI Blog,  SQL Server

SQL Server: how to list filegroup of tables and indexes

If you have a database with multiple physical files, you can group each file in a filegroup and store a table or a clustered index in the filegroup you prefer.

The following query reports for every table the filegroup when is stored and the table dimension in MB:


SELECT
FILEGROUP_NAME(AU.data_space_id) AS FileGroup,
OBJECT_NAME(Parti.object_id) AS TableName,
ind.name AS ClusteredIndexName,
AU.total_pages/128 AS TableSize_MB,
AU.used_pages/128 AS UsedSize_MB,
AU.data_pages/128 AS DataSize_MB
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 FILEGROUP_NAME(AU.data_space_id), AU.total_pages/128 DESC

Leave a Reply

Your email address will not be published. Required fields are marked *