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