Archive for the ‘SQL Server 2008 Partitioning’ 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>

SET @Object_id=OBJECT_ID(‘Table Name’ ) 

SELECT OBJECT_NAME(si.object_id) TbName,,si.index_id,si.data_space_id,,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
si.data_space_id=sds. data_space_id
INNER JOIN sys.partitionssp
si.index_id=sp.index_id and si.object_id=sp. object_id
INNER JOIN sys.allocation_unitssau
sp.partition_id=sau. container_id
where si.object_id= @Object_id
GROUP By OBJECT_NAME(si.object_id) ,,si.index_id,si.data_space_id, ,
sau.container_id,sau.total_pages,sau.used_pages,sau. data_pages
ORDER BY sp.partition_number  






Automating Sliding Window Table Partitioning

April 22, 2010 Leave a comment

Tables configured with Partition Scheme storage

April 20, 2010 Leave a comment

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