Cool Tips‎ > ‎Data Partitioning‎ > ‎

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


ċ
Get detailed partitioning information.sql
(1k)
Andy Hughes,
Jun 23, 2012, 2:00 AM
Comments