Data Partitioning - Get Partition Details

Applicability:

SQL Server 2000: Not Supported**

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 31 Jul 2009

Description

This code retrieves a list of key data partition metadata for all tables in a database.

** NOTE: The only data partitioning that was supported in SQL 2000 was via distributed partitioned views. They do not have the same level of functionality so there is no equivalent code.

Code

SELECT

tbl.name AS TableName,

p.partition_number AS PartitionNumber,

prv.value AS RightBoundaryValue,

CAST(p.rows AS FLOAT) AS [RowCount],

fg.name AS FileGroupName,

CAST(pf.boundary_value_on_right AS INT) AS RangeType,

p.data_compression AS DataCompression

FROM

sys.tables AS tbl

INNER JOIN sys.indexes AS idx

ON idx.object_id = tbl.object_id

INNER JOIN sys.partitions AS p

ON p.object_id=CAST(tbl.object_id AS INT)

AND p.index_id=idx.index_id

INNER JOIN sys.indexes AS indx

ON p.object_id = indx.object_id

and p.index_id = indx.index_id

LEFT OUTER JOIN sys.destination_data_spaces AS dds

ON dds.partition_scheme_id = indx.data_space_id

and dds.destination_id = p.partition_number

LEFT OUTER JOIN sys.partition_schemes AS ps

ON ps.data_space_id = indx.data_space_id

LEFT OUTER JOIN sys.partition_range_values AS prv

ON prv.boundary_id = p.partition_number

and prv.function_id = ps.function_id

LEFT OUTER JOIN sys.filegroups AS fg

ON fg.data_space_id = dds.data_space_id

or fg.data_space_id = indx.data_space_id

LEFT OUTER JOIN sys.partition_functions AS pf

ON pf.function_id = prv.function_id