> ## Documentation Index > Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # SHOW PARTITIONS export const InternalLink = ({version, path = "", children, ...props}) => { let detectedVersion = version || "stable"; if (typeof window !== 'undefined' && !version) { const match = window.location.pathname.match(/\/docs\/([^/]+)/); if (match) { detectedVersion = match[1]; } } const normalizedPath = path.startsWith("/") ? path.slice(1) : path; return {children} ; }; Use the `SHOW PARTITIONS` statement to view details about existing partitions. Most users should not need to use partitioning directly. Instead, they should use CockroachDB's built-in multi-region capabilities, which automatically handle geo-partitioning and other low-level details. 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 show_partitions syntax diagram ## 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 `movr` database. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo --geo-partitioned-replicas ``` The `movr` database in this example is pre-partitioned. For information about partitioning tables, see Define Table Partitions or `ALTER TABLE... PARTITION BY`. ### Show table partitions ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW PARTITIONS FROM TABLE users; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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@users_pkey | ('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@users_pkey | ('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@users_pkey | ('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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CREATE TABLE users; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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@users_pkey CONFIGURE ZONE USING | constraints = '[+region=europe-west1]'; | ALTER PARTITION us_east OF INDEX movr.public.users@users_pkey CONFIGURE ZONE USING | constraints = '[+region=us-east1]'; | ALTER PARTITION us_west OF INDEX movr.public.users@users_pkey 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. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > 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; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW CREATE TABLE users; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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 ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW PARTITIONS FROM INDEX vehicles@vehicles_auto_index_fk_city_ref_users; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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 ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW PARTITIONS FROM DATABASE movr; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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@users_pkey | ('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@users_pkey | ('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@users_pkey | ('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@vehicles_pkey | ('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_pkey | ('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_pkey | ('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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SELECT * FROM crdb_internal.partitions WHERE name='us_west'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > 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'; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} 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 * Low Latency Reads and Writes in a Multi-Region Cluster