Sql-server – Partition scheme filegroup metadata

partitioningsql-server-2008-r2system-tables

When you create a partition scheme against a particular partition function, you specify a series of filegroups. I cannot find the metadata store where this filegroup list can be found & queried.

For example, I execute

CREATE PARTITION FUNCTION [ABPF](int) AS RANGE LEFT FOR VALUES (10);
CREATE PARTITION SCHEME [ABPS] AS PARTITION [ABPF] TO ([PS1], [PS2], [PS3]);

I would like to then execute a query:

select * from <table(s)>

And return, PS1,PS2,PS3 in a table, preferably with an indicator that PS3 is the "next used" filegroup.

I've explored the following and cannot seem to see or find the answer:

  • sys.partition_functions
  • sys.partition_schemes
  • sys.partition_parameters
  • sys.partition_range_values
  • sys.partitions
  • sys.dm_db_partition_stats
  • sys.filegroups
  • sys.sysfiles
  • sys.database_files
  • sys.data_spaces
  • sys.allocation_units

Best Answer

This script that I have pulled from my script library (probably a Paul Randal script) gives you the file group information.

It also shows you the ranges of values between each boundary so all you would have to do to see which partition is next would be to get the MAX of your partitioning column (Probably the PK).

With this you could see which is the active partition and depending on your function being a left of value or right you could easily see which partition is next. At least I think that works in all situations.

SELECT      ps.name AS PartitionScheme
            , fg.name AS [FileGroup]
            , prv.*         
            , LAG(prv.Value) OVER (PARTITION BY ps.name ORDER BY ps.name, boundary_id) AS PreviousBoundaryValue

FROM        sys.partition_schemes ps
INNER JOIN  sys.destination_data_spaces dds
            ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN  sys.filegroups fg
            ON dds.data_space_id = fg.data_space_id
INNER JOIN  sys.partition_functions f
            ON f.function_id = ps.function_id
INNER JOIN  sys.partition_range_values prv
            ON f.function_id = prv.function_id
            AND dds.destination_id = prv.boundary_id
Related Question