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

 

 

Leave a Reply

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