CFS_developer
asked on
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.
Thanks!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
There are three catalog views for retrieving partition function metadata: sys.partition_functions, sys.partition_parameters and sys.partition_range_values
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_space
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.
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_space
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.
ASKER