Query Which Tables are Partitioned

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]

7 comments
Praveen 31 Oct 2011 at 11:55 am

There queries are very useful.
Thank You.

Ivaylo Byalkov 19 Oct 2011 at 3:00 am

Thank you! Very useful queries.

jon 05 Sep 2011 at 2:21 am

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)

Derek Dieter 16 Sep 2011 at 1:32 pm

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’)

Shrikant Bang 10 Aug 2011 at 2:47 am

Very useful information.
Thank You.

hari 31 Jul 2013 at 11:17 am

gud one

you 07 Jul 2015 at 2:51 pm

Featured Articles

 Site Author

  • Thanks for visiting!