# SHOW PARTITIONS

Use the `SHOW PARTITIONS` statement to view details about existing partitions.

Note:

This is an enterprise-only feature. You can use free trial credits to try it out.

Tip:

In testing, scripting, and other programmatic environments, we recommend querying the `crdb_internal.partitions` internal table for partition information instead of using the `SHOW PARTITIONS` statement. For more information, see Querying partitions programmatically.

## Synopsis

## Required privileges

No privileges are required to list partitions.

## Parameters

Parameter | Description
--- | ---
`database_name` | The name of the database for which to show partitions.
`table_name` | The name of the table for which to show partitions.
`table_index_name` | The name of the index for which to show partitions.

## Response

The following fields are returned for each partition:

Field | Description
--- | ---
`database_name` | The name of the database that contains the partition.
`table_name` | The name of the table that contains the partition.
`partition_name` | The name of the partition.
`parent_partition` | The name of the parent partition, if the partition is a subpartition.
`column_names` | The names of the columns in the partition definition expression.
`index_name` | The name of the index for the partition.
`partition_value` | The value that defines the partition.
`zone_constraints` | The zone constraints, if replication zones are configured for the partition.

## Examples

### Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run `cockroach demo` with the `--geo-partitioned-replicas` flag. This command opens an interactive SQL shell to a temporary, 9-node in-memory cluster with the Geo-Partitioned Replicas Topology applied to the `movr` database.

    $ cockroach demo --geo-partitioned-replicas

The `movr` database in this example is pre-partitioned. For information about partitioning tables, see Define Table Partitions or `PARTITION BY`.

### Show table partitions

    > SHOW PARTITIONS FROM TABLE users;

      database_name | table_name | partition_name | parent_partition | column_names |  index_name   |                 partition_value                 |              zone_config               |            full_zone_config
    +---------------+------------+----------------+------------------+--------------+---------------+-------------------------------------------------+----------------------------------------+-----------------------------------------+
      movr          | users      | us_west        | NULL             | city         | users@primary | ('seattle'), ('san francisco'), ('los angeles') | constraints = '[+region=us-west1]'     | range_min_bytes = 134217728,
                    |            |                |                  |              |               |                                                 |                                        | range_max_bytes = 536870912,
                    |            |                |                  |              |               |                                                 |                                        | gc.ttlseconds = 90000,
                    |            |                |                  |              |               |                                                 |                                        | num_replicas = 3,
                    |            |                |                  |              |               |                                                 |                                        | constraints = '[+region=us-west1]',
                    |            |                |                  |              |               |                                                 |                                        | lease_preferences = '[]'
      movr          | users      | us_east        | NULL             | city         | users@primary | ('new york'), ('boston'), ('washington dc')     | constraints = '[+region=us-east1]'     | range_min_bytes = 134217728,
                    |            |                |                  |              |               |                                                 |                                        | range_max_bytes = 536870912,
                    |            |                |                  |              |               |                                                 |                                        | gc.ttlseconds = 90000,
                    |            |                |                  |              |               |                                                 |                                        | num_replicas = 3,
                    |            |                |                  |              |               |                                                 |                                        | constraints = '[+region=us-east1]',
                    |            |                |                  |              |               |                                                 |                                        | lease_preferences = '[]'
      movr          | users      | europe_west    | NULL             | city         | users@primary | ('amsterdam'), ('paris'), ('rome')              | constraints = '[+region=europe-west1]' | range_min_bytes = 134217728,
                    |            |                |                  |              |               |                                                 |                                        | range_max_bytes = 536870912,
                    |            |                |                  |              |               |                                                 |                                        | gc.ttlseconds = 90000,
                    |            |                |                  |              |               |                                                 |                                        | num_replicas = 3,
                    |            |                |                  |              |               |                                                 |                                        | constraints = '[+region=europe-west1]',
                    |            |                |                  |              |               |                                                 |                                        | lease_preferences = '[]'
    (3 rows)

You can also use `SHOW CREATE TABLE` to view partitions on a table:

    > SHOW CREATE TABLE users;

      table_name |                                  create_statement
    +------------+-------------------------------------------------------------------------------------+
      users      | CREATE TABLE users (
                 |     id UUID NOT NULL,
                 |     city VARCHAR NOT NULL,
                 |     name VARCHAR NULL,
                 |     address VARCHAR NULL,
                 |     credit_card VARCHAR NULL,
                 |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
                 |     FAMILY "primary" (id, city, name, address, credit_card)
                 | ) PARTITION BY LIST (city) (
                 |     PARTITION us_west VALUES IN (('seattle'), ('san francisco'), ('los angeles')),
                 |     PARTITION us_east VALUES IN (('new york'), ('boston'), ('washington dc')),
                 |     PARTITION europe_west VALUES IN (('amsterdam'), ('paris'), ('rome'))
                 | );
                 | ALTER PARTITION europe_west OF INDEX movr.public.users@primary CONFIGURE ZONE USING
                 |     constraints = '[+region=europe-west1]';
                 | ALTER PARTITION us_east OF INDEX movr.public.users@primary CONFIGURE ZONE USING
                 |     constraints = '[+region=us-east1]';
                 | ALTER PARTITION us_west OF INDEX movr.public.users@primary CONFIGURE ZONE USING
                 |     constraints = '[+region=us-west1]'
    (1 row)

If a partitioned table has no zones configured, the `SHOW CREATE TABLE` output includes a warning.

    > ALTER PARTITION us_west OF TABLE users CONFIGURE ZONE DISCARD;
      ALTER PARTITION us_east OF TABLE users CONFIGURE ZONE DISCARD;
      ALTER PARTITION europe_west OF TABLE users CONFIGURE ZONE DISCARD;

    > SHOW CREATE TABLE users;

      table_name |                                  create_statement
    +------------+-------------------------------------------------------------------------------------+
      users      | CREATE TABLE users (
                 |     id UUID NOT NULL,
                 |     city VARCHAR NOT NULL,
                 |     name VARCHAR NULL,
                 |     address VARCHAR NULL,
                 |     credit_card VARCHAR NULL,
                 |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
                 |     FAMILY "primary" (id, city, name, address, credit_card)
                 | ) PARTITION BY LIST (city) (
                 |     PARTITION us_west VALUES IN (('seattle'), ('san francisco'), ('los angeles')),
                 |     PARTITION us_east VALUES IN (('new york'), ('boston'), ('washington dc')),
                 |     PARTITION europe_west VALUES IN (('amsterdam'), ('paris'), ('rome'))
                 | )
                 | -- Warning: Partitioned table with no zone configurations.
    (1 row)

### Show partitions by index

    > SHOW PARTITIONS FROM INDEX vehicles@vehicles_auto_index_fk_city_ref_users;

      database_name | table_name | partition_name | parent_partition | column_names |                   index_name                   |                 partition_value                 |              zone_config               |            full_zone_config
    +---------------+------------+----------------+------------------+--------------+------------------------------------------------+-------------------------------------------------+----------------------------------------+-----------------------------------------+
      movr          | vehicles   | us_west        | NULL             | city         | vehicles@vehicles_auto_index_fk_city_ref_users | ('seattle'), ('san francisco'), ('los angeles') | constraints = '[+region=us-west1]'     | range_min_bytes = 134217728,
                    |            |                |                  |              |                                                |                                                 |                                        | range_max_bytes = 536870912,
                    |            |                |                  |              |                                                |                                                 |                                        | gc.ttlseconds = 90000,
                    |            |                |                  |              |                                                |                                                 |                                        | num_replicas = 3,
                    |            |                |                  |              |                                                |                                                 |                                        | constraints = '[+region=us-west1]',
                    |            |                |                  |              |                                                |                                                 |                                        | lease_preferences = '[]'
      movr          | vehicles   | us_east        | NULL             | city         | vehicles@vehicles_auto_index_fk_city_ref_users | ('new york'), ('boston'), ('washington dc')     | constraints = '[+region=us-east1]'     | range_min_bytes = 134217728,
                    |            |                |                  |              |                                                |                                                 |                                        | range_max_bytes = 536870912,
                    |            |                |                  |              |                                                |                                                 |                                        | gc.ttlseconds = 90000,
                    |            |                |                  |              |                                                |                                                 |                                        | num_replicas = 3,
                    |            |                |                  |              |                                                |                                                 |                                        | constraints = '[+region=us-east1]',
                    |            |                |                  |              |                                                |                                                 |                                        | lease_preferences = '[]'
      movr          | vehicles   | europe_west    | NULL             | city         | vehicles@vehicles_auto_index_fk_city_ref_users | ('amsterdam'), ('paris'), ('rome')              | constraints = '[+region=europe-west1]' | range_min_bytes = 134217728,
                    |            |                |                  |              |                                                |                                                 |                                        | range_max_bytes = 536870912,
                    |            |                |                  |              |                                                |                                                 |                                        | gc.ttlseconds = 90000,
                    |            |                |                  |              |                                                |                                                 |                                        | num_replicas = 3,
                    |            |                |                  |              |                                                |                                                 |                                        | constraints = '[+region=europe-west1]',
                    |            |                |                  |              |                                                |                                                 |                                        | lease_preferences = '[]'
    (3 rows)

### Show partitions by database

    > SHOW PARTITIONS FROM DATABASE movr;

      database_name | table_name | partition_name | parent_partition | column_names |    index_name    |                 partition_value                 |              zone_config               |            full_zone_config
    +---------------+------------+----------------+------------------+--------------+------------------+-------------------------------------------------+----------------------------------------+-----------------------------------------+
      movr          | users      | us_west        | NULL             | city         | users@primary    | ('seattle'), ('san francisco'), ('los angeles') | NULL                                   | range_min_bytes = 134217728,
                    |            |                |                  |              |                  |                                                 |                                        | range_max_bytes = 536870912,
                    |            |                |                  |              |                  |                                                 |                                        | gc.ttlseconds = 90000,
                    |            |                |                  |              |                  |                                                 |                                        | num_replicas = 3,
                    |            |                |                  |              |                  |                                                 |                                        | constraints = '[]',
                    |            |                |                  |              |                  |                                                 |                                        | lease_preferences = '[]'
      movr          | users      | us_east        | NULL             | city         | users@primary    | ('new york'), ('boston'), ('washington dc')     | NULL                                   | range_min_bytes = 134217728,
                    |            |                |                  |              |                  |                                                 |                                        | range_max_bytes = 536870912,
                    |            |                |                  |              |                  |                                                 |                                        | gc.ttlseconds = 90000,
                    |            |                |                  |              |                  |                                                 |                                        | num_replicas = 3,
                    |            |                |                  |              |                  |                                                 |                                        | constraints = '[]',
                    |            |                |                  |              |                  |                                                 |                                        | lease_preferences = '[]'
      movr          | users      | europe_west    | NULL             | city         | users@primary    | ('amsterdam'), ('paris'), ('rome')              | NULL                                   | range_min_bytes = 134217728,
                    |            |                |                  |              |                  |                                                 |                                        | range_max_bytes = 536870912,
                    |            |                |                  |              |                  |                                                 |                                        | gc.ttlseconds = 90000,
                    |            |                |                  |              |                  |                                                 |                                        | num_replicas = 3,
                    |            |                |                  |              |                  |                                                 |                                        | constraints = '[]',
                    |            |                |                  |              |                  |                                                 |                                        | lease_preferences = '[]'
      movr          | vehicles   | us_west        | NULL             | city         | vehicles@primary | ('seattle'), ('san francisco'), ('los angeles') | constraints = '[+region=us-west1]'     | range_min_bytes = 134217728,
                    |            |                |                  |              |                  |                                                 |                                        | range_max_bytes = 536870912,
                    |            |                |                  |              |                  |                                                 |                                        | gc.ttlseconds = 90000,
                    |            |                |                  |              |                  |                                                 |                                        | num_replicas = 3,
                    |            |                |                  |              |                  |                                                 |                                        | constraints = '[+region=us-west1]',
                    |            |                |                  |              |                  |                                                 |                                        | lease_preferences = '[]'
      movr          | vehicles   | us_east        | NULL             | city         | vehicles@primary | ('new york'), ('boston'), ('washington dc')     | constraints = '[+region=us-east1]'     | range_min_bytes = 134217728,
                    |            |                |                  |              |                  |                                                 |                                        | range_max_bytes = 536870912,
                    |            |                |                  |              |                  |                                                 |                                        | gc.ttlseconds = 90000,
                    |            |                |                  |              |                  |                                                 |                                        | num_replicas = 3,
                    |            |                |                  |              |                  |                                                 |                                        | constraints = '[+region=us-east1]',
                    |            |                |                  |              |                  |                                                 |                                        | lease_preferences = '[]'
      movr          | vehicles   | europe_west    | NULL             | city         | vehicles@primary | ('amsterdam'), ('paris'), ('rome')              | constraints = '[+region=europe-west1]' | range_min_bytes = 134217728,
                    |            |                |                  |              |                  |                                                 |                                        | range_max_bytes = 536870912,
                    |            |                |                  |              |                  |                                                 |                                        | gc.ttlseconds = 90000,
                    |            |                |                  |              |                  |                                                 |                                        | num_replicas = 3,
                    |            |                |                  |              |                  |                                                 |                                        | constraints = '[+region=europe-west1]',
                    |            |                |                  |              |                  |                                                 |                                        | lease_preferences = '[]'
    ...
    (24 rows)

## Querying partitions programmatically

The `crdb_internal.partitions` internal table contains information about the partitions in your database. In testing, scripting, and other programmatic environments, we recommend querying this table for partition information instead of using the `SHOW PARTITIONS` statement. For example, to get all `us_west` partitions of in your database, you can run the following query:

    > SELECT * FROM crdb_internal.partitions WHERE name='us_west';

      table_id | index_id | parent_name |  name   | columns | column_names |                   list_value                    | range_value | zone_id | subzone_id
    +----------+----------+-------------+---------+---------+--------------+-------------------------------------------------+-------------+---------+------------+
            53 |        1 | NULL        | us_west |       1 | city         | ('seattle'), ('san francisco'), ('los angeles') | NULL        |       0 |          0
            54 |        1 | NULL        | us_west |       1 | city         | ('seattle'), ('san francisco'), ('los angeles') | NULL        |      54 |          1
            54 |        2 | NULL        | us_west |       1 | city         | ('seattle'), ('san francisco'), ('los angeles') | NULL        |      54 |          2
            55 |        1 | NULL        | us_west |       1 | city         | ('seattle'), ('san francisco'), ('los angeles') | NULL        |      55 |          1
            55 |        2 | NULL        | us_west |       1 | city         | ('seattle'), ('san francisco'), ('los angeles') | NULL        |      55 |          2
            55 |        3 | NULL        | us_west |       1 | vehicle_city | ('seattle'), ('san francisco'), ('los angeles') | NULL        |      55 |          3
            56 |        1 | NULL        | us_west |       1 | city         | ('seattle'), ('san francisco'), ('los angeles') | NULL        |      56 |          1
            58 |        1 | NULL        | us_west |       1 | city         | ('seattle'), ('san francisco'), ('los angeles') | NULL        |      58 |          1
    (8 rows)

Other internal tables, like `crdb_internal.tables`, include information that could be useful in conjunction with `crdb_internal.partitions`.

For example, if you want the output for your partitions to include the name of the table and database, you can perform a join of the two tables:

    > SELECT
      partitions.name AS partition_name, column_names, list_value, tables.name AS table_name, database_name
      FROM crdb_internal.partitions JOIN crdb_internal.tables ON partitions.table_id=tables.table_id
      WHERE tables.name='users';

      partition_name | column_names |                   list_value                    | table_name | database_name
    +----------------+--------------+-------------------------------------------------+------------+---------------+
      us_west        | city         | ('seattle'), ('san francisco'), ('los angeles') | users      | movr
      us_east        | city         | ('new york'), ('boston'), ('washington dc')     | users      | movr
      europe_west    | city         | ('amsterdam'), ('paris'), ('rome')              | users      | movr
    (3 rows)

## See also

- Define Table Partitions
- SQL Statements
- Geo-Partitioning