Thursday, October 27, 2011

Partitioned Table Metadata

I wanted a view that gave me all of the information about partitioned tables in one place. This data would give me everything I needed to monitor and maintain the table and have all of the relevant columns to let me use it to dynamically script new partitions as needed.

SELECT object_name(p.object_id) [TableName]
,p.partition_number   [PartitionNumber]
  ,p.rows   [Rows]
,au.total_pages   [TotalPages]
,au.used_pages   [UsedPages]
,au.data_pages   [DatePages]
,   [FileGroupName]
,   [FileName]
,mf.physical_name   [FilePhysicalName]
,   [PartionSchemaName]
,                 [PatitionFunctionName]
,pf.type_desc   [PartitionFunctionType]
,pf.fanout [PartitionFunctionFanout]
,pf.boundary_value_on_right  [PartitionFunctionBoundryOnRight]
,prv.Value         [RangeValue]
FROM   sys.partitions  p
JOIN   sys.indexes i
ON   p.object_id = i.object_id
AND   p.index_id = i.index_id
AND   i.type in (0,1) --0 = heap, 1 = clustered, skip the nonclustered for the count
JOIN   sys.partition_schemes AS ps
ON   ps.data_space_id = i.data_space_id
JOIN   sys.partition_functions AS pf
ON  ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values AS prv
ON   prv.function_id = pf.function_id
AND   p.partition_number = prv.boundary_id + isnull(pf.boundary_value_on_right,0)+1
JOIN   sys.allocation_units au
ON   (au.type in (1,3) AND p.partition_id = au.container_id)
OR   (au.type in (2) AND p.hobt_id = au.container_id)
JOIN   sys.filegroups fg
ON   fg.data_space_id = au.data_space_id
JOIN   sys.master_files mf
ON   mf.data_space_id = au.data_space_id
AND   mf.database_id = DB_ID()

1 comment:

  1. I found a problem when it comes to the multi-parameter aggregates, It seems that they are only allowed in SQL2008+. I am currently working on a re-write of the CLR functions that will use one parameter to provide the same functionality.