Find Partition Schema Definitions in SQL Server Database

Question

I have access to a database and I need to know the Partition Scheme definitions in the database. i.e. I need to know the partition scheme name, which Partition function is it using, what file groups are the partitions assigned, etc...

For example someone creates a partition scheme as so (taken from msdn):

CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg);

Then I want the name: myRangePS1, the function: myRangePF1, and the partitions: (test1fg, test2fg, test3fg, test4fg), Whether it is partition ALL or not

How would I go about this using SQL statements only? I can query the names and some data about partitions by using the system view sys.partition_scheme, but it is not enough.

The below shows a similar solution on finding the definition of Partition functions: http://social.msdn.microsoft.com/forums/sqlserver/en-US/d0ce92e3-bf48-455d-bd89-c334654d7e97/how-to-find-partition-function-text-applied-to-a-table

Atwood

Answer 1

I have modified knkarthick24's first query to show Partition function values associated to each file group:

select distinct ps.Name AS PartitionScheme, pf.name AS PartitionFunction,fg.name AS FileGroupName, rv.value AS PartitionFunctionValue
    from sys.indexes i  
    join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id  
    join sys.partition_schemes ps on ps.data_space_id = i.data_space_id  
    join sys.partition_functions pf on pf.function_id = ps.function_id  
    left join sys.partition_range_values rv on rv.function_id = pf.function_id AND rv.boundary_id = p.partition_number
    join sys.allocation_units au  ON au.container_id = p.hobt_id   
    join sys.filegroups fg  ON fg.data_space_id = au.data_space_id  
where i.object_id = object_id('TableName') 

This is the query I was looking for and I hope other people can make use of this!

Atwood

Answer 2

Please try this query: 1)

  select ps.Name AS PartitionScheme, pf.name AS PartitionFunction,fg.name AS FileGroupName  
     from sys.indexes i  
     JOIN sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id  
     join sys.partition_schemes ps on ps.data_space_id = i.data_space_id  
     join sys.partition_functions pf on pf.function_id = ps.function_id  
     join sys.allocation_units au  ON au.container_id = p.hobt_id   
     join sys.filegroups fg  ON fg.data_space_id = au.data_space_id  
    where i.object_id = object_id('TableName') 

or for more detailed information use the below query( SQL 2008 Internals Book)

2)

SELECT 
ISNULL(quotename(ix.name),'Heap') as IndexName 
,ix.type_desc as type
,prt.partition_number
,prt.data_compression_desc
,ps.name as PartitionScheme
,pf.name as PartitionFunction
,fg.name as FilegroupName
,case when ix.index_id < 2 then prt.rows else 0 END as Rows
,au.TotalMB
,au.UsedMB
,case when pf.boundary_value_on_right = 1 then 'less than' when pf.boundary_value_on_right is null then '' else 'less than or equal to' End as Comparison
,fg.name as FileGroup
,rv.value
FROM sys.partitions prt
inner join sys.indexes ix
on ix.object_id = prt.object_id and
ix.index_id = prt.index_id
inner join sys.data_spaces ds
on ds.data_space_id = ix.data_space_id
left join sys.partition_schemes ps
on ps.data_space_id = ix.data_space_id
left join sys.partition_functions pf
on pf.function_id = ps.function_id
left join sys.partition_range_values rv
on rv.function_id = pf.function_id AND
rv.boundary_id = prt.partition_number
left join sys.destination_data_spaces dds
on dds.partition_scheme_id = ps.data_space_id AND
dds.destination_id = prt.partition_number
left join sys.filegroups fg
on fg.data_space_id = ISNULL(dds.data_space_id,ix.data_space_id)
inner join (select str(sum(total_pages)*8./1024,10,2) as [TotalMB]
,str(sum(used_pages)*8./1024,10,2) as [UsedMB]
,container_id
from sys.allocation_units
group by container_id) au
on au.container_id = prt.partition_id
WHERE prt.OBJECT_ID = object_id(N'dbo.test')
order by ix.type_desc;

Bakelite

Hey thanks! The first query was missinge info on which partition function section belonged to which filegroup, but the second query had this info – Atwood

Answer 3

I modified the knkarthick24 script to make it a little more user-friendly in understanding the function definition. In addition, my script returns data for all tables that have been partitioned the user:

SELECT 
ISNULL(quotename(ix.name),'Heap') as IndexName 
,ix.type_desc as type
,prt.partition_number
,prt.data_compression_desc
,ps.name as PartitionScheme
,pf.name as PartitionFunction
,case when ix.index_id < 2 then prt.rows else 0 END as Rows
,au.TotalMB
,au.UsedMB
,s.[name]+'.'+t.[name] [TableName]
,c.name  [PartByColName]
,case when (pf.boundary_value_on_right = 1 and rv.value is not null) then '<' when (pf.boundary_value_on_right = 0 and rv.value is not null) then '<=' else '' End as term
,rv.value
,fg.name as FilegroupName
FROM sys.partitions prt
inner join sys.indexes ix
    on ix.object_id = prt.object_id and ix.index_id = prt.index_id
inner join sys.tables t
    on t.object_id = ix.object_id AND ix.type IN (0,1)
JOIN sys.index_columns AS ic   
    ON ic.[object_id] = ix.[object_id] AND ic.index_id = ix.index_id AND ic.partition_ordinal >= 1 
JOIN sys.columns AS c   
    ON t.[object_id] = c.[object_id] AND ic.column_id = c.column_id 
inner join sys.schemas s 
    ON s.schema_id=t.schema_id
inner join sys.data_spaces ds
    on ds.data_space_id = ix.data_space_id
left join sys.partition_schemes ps
    on ps.data_space_id = ix.data_space_id
left join sys.partition_functions pf
    on pf.function_id = ps.function_id
left join sys.partition_range_values rv
    on rv.function_id = pf.function_id AND rv.boundary_id = prt.partition_number
left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = prt.partition_number
left join sys.filegroups fg
    on fg.data_space_id = ISNULL(dds.data_space_id,ix.data_space_id)
inner join (select str(sum(total_pages)*8./1024,10,2) as [TotalMB],str(sum(used_pages)*8./1024,10,2) as [UsedMB],container_id from sys.allocation_units group by container_id) au
    on au.container_id = prt.partition_id
where pf.is_system=0
order by ps.name,rv.value,fg.name

Mouser