List partitions in a Sql Server table

Posted on 2014-07-10
Last Modified: 2014-07-10
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.

Question by:CFS_developer
    LVL 68

    Accepted Solution

    I take it you mean the data space names and not the partition function name.

    You can get the data space names from:

    Author Comment

    That's what I needed!
    LVL 1

    Expert Comment

    by:Pooja Katiyar Verma
    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now