Cool Tips‎ > ‎Data Partitioning‎ > ‎

Data Partitioning - Get Partition Details


                 SQL Server 2000:        Not Supported**
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        


Author:    Unknown
Date:        31 Jul 2009


 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.



SELECT                                AS TableName,
p.partition_number                      AS PartitionNumber,
prv.value                               AS RightBoundaryValue,
CAST(p.rows AS FLOAT)                 AS [RowCount],                                 AS FileGroupName,
CAST(pf.boundary_value_on_right AS INT) AS RangeType,  
p.data_compression                      AS DataCompression
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)
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

Andy Hughes,
Jun 23, 2012, 2:00 AM