This query is quite useful when looking at which partitions are in the database. It is originally taken from the book Microsoft SQL Server 2008 Internals (p. 438), and then I have tweaked it a bit and added some more information.
I used this query in my demo today at SQLSaturday #152 in Bulgaria, where I talked about layered partitioning.
DECLARE @TableName NVARCHAR(200) = N'Facts.Credit' SELECT SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(i.object_id) AS [object] , p.partition_number AS [p#] , fg.name AS [filegroup] , p.rows , au.total_pages AS pages , CASE boundary_value_on_right WHEN 1 THEN 'less than' ELSE 'less than or equal to' END as comparison , rv.value , CONVERT (VARCHAR(6), CONVERT (INT, SUBSTRING (au.first_page, 6, 1) + SUBSTRING (au.first_page, 5, 1))) + ':' + CONVERT (VARCHAR(20), CONVERT (INT, SUBSTRING (au.first_page, 4, 1) + SUBSTRING (au.first_page, 3, 1) + SUBSTRING (au.first_page, 2, 1) + SUBSTRING (au.first_page, 1, 1))) AS first_page FROM sys.partitions p INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id INNER JOIN sys.objects o ON p.object_id = o.object_id INNER JOIN sys.system_internals_allocation_units au ON p.partition_id = au.container_id INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id INNER JOIN sys.partition_functions f ON f.function_id = ps.function_id INNER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number INNER JOIN sys.filegroups fg ON dds.data_space_id = fg.data_space_id LEFT OUTER JOIN sys.partition_range_values rv ON f.function_id = rv.function_id AND p.partition_number = rv.boundary_id WHERE i.index_id < 2 AND o.object_id = OBJECT_ID(@TableName);
The result is something like the following.
The query shows:
- object – The table name.
- p# – The partition number.
- filegroup – The filegroup the partition is located on. Note that in this result, I am using the same filegroup for all partitions, but usually you would probably not do this.
- rows – Number of rows in the partition.
- comparison – shows “less than” if you are using right range partitioning or “less than or equal to” if you are using left range partitioning.
- value – The boundary point between the two partitions. This value is either in the right or left partition of the boundary point, depending on whether or not you are using right range or left range partitioning.
- first_page – The first file:page allocated for the partition.
[…] is from a learner. Chris and Manuj also explained and helped a lot. This was also useful – https://davidpeterhansen.com/view-partitions-in-sql-server/ , there is a problem with this query in that it doesn’t consider scheme qualified tables, I […]
LikeLike
Hi,
I use a modified version of your query here – http://paulbrewer.wordpress.com/2014/03/06/table-partitioning-bug-merge-range-switch-out/ , it’s great but it needs to include the scheme name in the result set.
Thanks and best wishes
Paul
LikeLike
Great script – thanks for sharing!
LikeLike
Thanks for sharing David – this have saved me for some TSQL coding 🙂
I hope that you are well arrived in Hong kong.
LikeLike