How to get Partitioned Tables Storage Info

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

 

 

 

 

Automating Sliding Window Table Partitioning

Tables configured with Partition Scheme storage

How to get information about tables configured with database partition scheme or table configured to use database partitioning using partition scheme and function.

I have a database with more than 3000 tables which are being partitioned, while automating the partitioning feature I came across a situation when I need info about what all tables are configured with database partitioning (partition scheme).

Below code helped me doing so, Write this blog thinking this will help others as well.

DECLARE @PartitionSchemeId varchar (max)

 SELECT @PartitionSchemeId=data_space_id from sys.data_spaces where type=’PS’

Print @PartitionSchemeId

 SELECT * FROM sys.tables where lob_data_space_id=@PartitionSchemeId