List out which tables are partitioned, and what partition scheme and partition function they use:
select t.name as TableName, ps.name as PartitionScheme, ps.data_space_id, pf.name as PartitionFunction, pf.function_id from sys.tables t join sys.indexes i on t.object_id = i.object_id join sys.partition_schemes ps on i.data_space_id = ps.data_space_id join sys.partition_functions pf on ps.function_id = pf.function_id where i.index_id < 2
For a given partition function, list each of the boundary values
select r.boundary_id, r.value from sys.partition_range_values r join sys.partition_functions pf on r.function_id = pf.function_id where pf.name = 'fnQuarters' -- partition function name order by r.value
Popular search terms:
There queries are very useful.
Thank You.
Thank you! Very useful queries.
That’s great. How do I find the column name that the table uses in the partition? i.e. where does sql server record that it is the DueDate column (see below) that is used by the partition schema.
create table PartitionedOrders
(
Id int not null identity(1,1),
DueDate DateTime not null,
) on YearPS(DueDate)
Hi Jon,
This should work:
select c.name
from sys.partitions sp
JOIN sys.index_columns ic
ON ic.object_id = sp.object_id
JOIN sys.columns c
ON c.OBJECT_ID = ic.OBJECT_ID AND c.column_id = ic.column_id
WHERE sp.object_id = object_id(‘PartitionedOrders’)
Very useful information.
Thank You.