How to get Partitioned Tables Storage Info

Query to get Partitioned Objects and Partition information

 USE <Database Name>
GO

DECLARE @Object_id int
SET @Object_id=OBJECT_ID('Table Name' ) 

SELECT OBJECT_NAME(si.object_id) TbName,si.name,si.index_id,si.data_space_id,sds.name,sp.index_id ,
                 sp.partition_id,sp.partition_number,sp.rows,sau.container_id,sau.total_pages,sau.used_pages,sau. data_pages
from sys.indexes si
         INNER JOIN sys.data_spaces sds
                 ON  si.data_space_id=sds. data_space_id
         INNER JOIN sys.partitions sp
                ON si.index_id=sp.index_id and si.object_id=sp. object_id
         INNER JOIN sys.allocation_units sau
               ON sp.partition_id=sau. container_id
                                 where si.object_id= @Object_id
        GROUP By OBJECT_NAME(si.object_id) ,si.name,si.index_id,si.data_space_id,sds.name ,
        sp.index_id,sp.partition_id,sp.partition_number,sp.rows ,
        sau.container_id,sau.total_pages,sau.used_pages,sau. data_pages
         ORDER BY sp.partition_number  
 GO

 

 

 

Leave a Reply

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