Archive

Archive for the ‘Partitioned Tables Storage Info’ Category

How to get Partitioned Tables Storage Info

November 21, 2010 1 comment

I hope this will help DBA in getting information regarding Partitioned Objects and Storage Information



 USE <DatabaseName>
GO

DECLARE@Object_idint
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_spacessds
                 ON 
si.data_space_id=sds. data_space_id
        
INNER JOIN sys.partitionssp
               
ON
si.index_id=sp.index_id and si.object_id=sp. object_id
        
INNER JOIN sys.allocation_unitssau
              
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