Tuesday, December 3, 2019

get partitioned by partition schema in database

OBJECT_SCHEMA_NAME(i.object_id) AS [Schema Name],
t.name AS [Table Name],
i.name AS [Index Name],
s.name AS [Partition Scheme], *
FROM sys.indexes i
INNER JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id
INNER JOIN sys.tables t ON i.object_id = t.object_id

Every table in SQL Server has a single partition. If you run above script it will list only those tables which are partitioned by partition scheme and have more than one partitions.