Query Which Tables are Partitioned
-
Posted on June 19, 2009 by Derek Dieter
-
0
List out which tables are partitioned, and what partition scheme and partition function they use:
[cc lang=”sql”]
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
[/cc]
For a given partition function, list each of the boundary values
[cc lang=”sql”]
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
[/cc]
- Comments (RSS)
- Trackback
- Permalink
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.
gud one
bhanchod
