Earlier today on twitter I ran across an interesting question. The question was, how to identify all partitions and their boundaries for a given filegroup. The solution is not as trivial as it may sound, so I will expand on it here a little.
First we need to find out which filegroup a partition is on. All partitions are listed in the sys.partitions view. That view has an object_id column that we can use to join to sys.tables. Joining to sys.tables makes sure we are dealing only with partitions of user tables. sys.partitions has a hobt_id column that allows us to join to sys.allocation_units on its container_id column. Each table and each index has at least one partition. All normal data pages of a partition are stored together in a single allocation unit. The sys.allocation_units view also has a data_space_id column that can be used to join to sys.filegroups.
To get from a partition to its partition function we need to first join sys.partitions with sys.indexes on both the object_id and on the index_id columns. sys.indexes also has a data_space_id column that we can use to join to sys.partition_schemes.data_space_id. From sys.partition_schemes we get to sys.partition_functions using the function_id column of both tables.
A partition function defines the boundaries of the partitions. The first partition does not have a lower boundary and the last partition does not have an upper boundary. Wether the boundary value itself belongs to its left or its right partition is specified by the LEFT or the RIGHT key word that was used at the time the partition function was created. It is always the same for all boundaries of a single partition function. Which one was used can be seen in the boundary_value_on_right column of the sys.partition_functions view. The actual boundary values can be found in the value column of the sys.partition_range_values view.
As each partition has up to two boundaries, we need to join to sys.partition_range_values twice. The function_id column ties it to the sys.partition_functions view. The boundary_id can be correlated with the partition_number column of sys.partitions. The first partition has boundary one as upper boundary; the second partition has boundary one as lower boundary and boundary two as upper boundary. So for the lower boundary the join condition has to be sys.partition_range_values.boundary_id + 1 = sys.partitions.partition_number and for the upper boundary it is sys.partition_range_values.boundary_id = sys.partitions.partition_number.
When putting this all together, you need to pay attention to the fact, that while every table has at least one partition, not everyone is located on a partition scheme. Also, not every partition of a partitioned table has both the upper and the lower boundary defined. That means that the four joins to the partition scheme related views have to be outer joins.
The final query can be found below.