Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 464
  • Last Modified:

List partitions in a Sql Server table

I have a 2014 Sql Server database with a partitioned table.  How do I query for the names of the partitions?  I found sys.partitions but that gave me the partition_id and not the partition_name.

1 Solution
Scott PletcherSenior DBACommented:
I take it you mean the data space names and not the partition function name.

You can get the data space names from:
CFS_developerAuthor Commented:
That's what I needed!
Pooja Katiyar VermaCommented:
There are three catalog views for retrieving partition function metadata: sys.partition_functions, sys.partition_parameters and sys.partition_range_values. The following table summarizes the columns available from each of these catalog views:

View      Column      Meaning
Partition_functions      Name      Partition function name.
Function_id      Partition function identifier unique across the database.
Type      Partition function type. For SQL Server 2005 type will always be "R" = range.
Type_desc      Partition function type description.
Fanout      Number of partitions created by partition function.
Boundary_value_on_right      Bit column specifying whether boundary values are specified on LEFT or RIGHT. If boundary is RIGHT then the value is 1, otherwise 0.
Create_date      Date partition function was created.
Modify_date      Date partition function was altered.
Partition_parameters      Function_id      Partition function identifier.
Parameter_id      Parameter identifier unique per partition function.
System_type_id      Data type identifier for the partition function parameter.
Max_length      Maximum length of the parameter with character data type.
Precision      Precision value for a parameter with numeric or float data type.
Scale      Scale value for a parameter with numeric or float data type.
Collation name      Collation name for the parameter with character data type.
Partition_range_values      Function_id      Partition function identifier.
Boundary_id      Boundary identifier, unique per partition function. Boundary ids start at 1.
Parameter_id      Parameter identifier for which the boundary is defined.
Value      Boundary value.
You can use $PARTITION function to:

Retrieve partition number (or identifier) for each partition.
Retrieve the count of rows for each partition that has at least one row.
Return all rows for a partition with the specific partition identifier.
You can use the following catalog views to retrieve data about partition schemes: sys.partition_schemes, sys.data_spaces and sys.destination_data_spaces. Sys.partition_schemes view contains only one column different from sys.data_spaces. The following table documents columns available in each of these catalog views:

View      Column      Description
Data_spaces      Name      Data space name, which must be unique across the database. Data space is either a filegroup or a partition scheme.
Data_space_id      Data space identifier, which must be unique across the database.
Type      Either filegroup (FG) or partition scheme (PS).
Type_desc      Data space type description. Can be either ROWS_FILEGROUP or PARTITION_SCHEME.
Is_default      Whether filegroup is the default filegroup for the database.
Partition_schemes      Function_id      Reference to the partition function identifier used by the current partition scheme.
Destination_data_spaces      Partition_scheme_id      Partition scheme identifier referencing data_space_id column in sys.data_spaces.
Destination_id      Ordinal number starting at 1 denoting the mapping of partition function boundary to filegroup.
Data_space_id      Filegroup id referencing data_space_id column in sys.data_spaces for mapping the partition function boundary to a filegroup.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now