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.

Who is Participating?
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.