Archive for the ‘Tables configured with Partition Scheme storage’ Category

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