> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SHOW REGIONS

The `SHOW REGIONS` [statement](https://docs.cockroachlabs.com/docs/stable/sql-statements.md) lists the [cluster regions](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#cluster-regions) for a multi-region cluster, or the [database regions](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#database-regions) for the databases in a multi-region cluster.

## Synopsis

![show\_regions syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/show_regions.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=6c66c1d2bdf1dc0b6815f48deafa4e8d)

## Required privileges

Only members of the [`admin` role](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization#admin-role) can run `SHOW REGIONS`. By default, the `root` user belongs to the `admin` role.

## Parameters

| Parameter | Description |
| - | - |
| `FROM CLUSTER` | Show the [cluster regions](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#cluster-regions) for a cluster. Cluster regions are specified [at cluster startup](https://docs.cockroachlabs.com/docs/stable/cockroach-start.md#locality). |
| `FROM DATABASE` | Show all [database regions](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#database-regions) for the current database. Database regions can be added [at database creation](https://docs.cockroachlabs.com/docs/stable/create-database.md), or [after a database has been created](https://docs.cockroachlabs.com/docs/stable/alter-database.md). |
| `FROM DATABASE database_name` | Show all database regions from the database named `database_name`. |
| `FROM ALL DATABASES` | Show the database regions for all databases in the cluster. |

## Response

`SHOW REGIONS`, `SHOW REGIONS FROM CLUSTER`, and `SHOW REGIONS FROM DATABASE` return the following fields for each region:

| Field | Description | `SHOW REGIONS` | `SHOW REGIONS FROM CLUSTER` | `SHOW REGIONS FROM DATABASE` |
| - | - | - | - | - |
| `region` | The name of the region. | ✓ | ✓ | ✓ |
| `zones` | The availability zones for the region. | ✓ | ✓ | ✓ |
| `database_names` | A set of database names that use the region. | ✓ | | |
| `primary_region_of` | A set of database names for which the region is the [primary region](https://docs.cockroachlabs.com/docs/stable/alter-database.md#set-primary-region). | ✓ | | |
| `secondary_region_of` | A set of database names for which the region is the [secondary region](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#secondary-regions). | ✓ | | |
| `database` | The name of the database that uses the region. | | | ✓ |
| `primary` | If `true`, indicates that the region is the primary region. | | | ✓ |

`SHOW REGIONS FROM ALL DATABASES` returns the following fields for each database:

| Field | Description |
| - | - |
| `database_name` | The name of the database. |
| `regions` | A set of region names in use by the database. |
| `primary_region` | The primary region of the database. |
| `secondary_region` | The [secondary region](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#secondary-regions) of the database. |

## 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](https://docs.cockroachlabs.com/docs/stable/movr.md).

To follow along, run [`cockroach demo`](https://docs.cockroachlabs.com/docs/stable/cockroach-demo) with the [`--nodes`](https://docs.cockroachlabs.com/docs/stable/cockroach-demo#flags) and [`--demo-locality`](https://docs.cockroachlabs.com/docs/stable/cockroach-demo#flags) flags. This command opens an interactive SQL shell to a temporary, multi-node in-memory cluster with the `movr` database preloaded and set as the [current database](https://docs.cockroachlabs.com/docs/stable/sql-name-resolution.md#current-database).

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo --nodes=6 --demo-locality=region=us-east,zone=us-east-a:region=us-east,zone=us-east-b:region=us-central,zone=us-central-a:region=us-central,zone=us-central-b:region=us-west,zone=us-west-a:region=us-west,zone=us-west-b
```

### View the regions in a cluster

After cluster startup, you can view all of the cluster regions available in the cluster with `SHOW REGIONS FROM CLUSTER`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW REGIONS FROM CLUSTER;
```

```
    region   |            zones
-------------+------------------------------
  us-central | {us-central-a,us-central-b}
  us-east    | {us-east-a,us-east-b}
  us-west    | {us-west-a,us-west-b}
(3 rows)
```

### View the regions in a single database

`SHOW REGIONS FROM DATABASE` returns the database regions for a specific database.

[Add an available region](https://docs.cockroachlabs.com/docs/stable/alter-database.md#add-region) as the primary region for the `movr` database:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr PRIMARY REGION "us-east";
```

```
ALTER DATABASE PRIMARY REGION
```

> [!NOTE]
>
> Only [cluster regions](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#cluster-regions) (i.e., regions that are defined at [node startup time](https://docs.cockroachlabs.com/docs/stable/cockroach-start.md#locality)) can be added to a multi-region database.

Then, add more regions to the database:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr ADD REGION "us-west";
```

```
ALTER DATABASE ADD REGION
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE movr ADD REGION "us-central";
```

```
ALTER DATABASE ADD REGION
```

To view the regions associated with the database:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW REGIONS FROM DATABASE movr;
```

```
  database |   region   | primary | secondary |            zones
-----------+------------+---------+-----------+------------------------------
  movr     | us-east    |    t    |     f     | {us-east-a,us-east-b}
  movr     | us-central |    f    |     f     | {us-central-a,us-central-b}
  movr     | us-west    |    f    |     f     | {us-west-a,us-west-b}
(3 rows)
```

The `secondary` column in each row says whether that region has been made a *secondary region* for failover purposes. For more information, see [Secondary regions](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#secondary-regions).

With `movr` set as the current database, the following statement returns the same results:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW REGIONS FROM DATABASE;
```

### View the regions for all databases in a cluster

Create another database in the cluster with a primary region:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE DATABASE cockroachlabs PRIMARY REGION "us-east";
```

Then, add another region to the database:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE cockroachlabs ADD REGION "us-west";
```

To show the regions in use by all the databases in a cluster, use `SHOW REGIONS`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW REGIONS;
```

```
    region   |            zones            |    database_names    |  primary_region_of
-------------+-----------------------------+----------------------+-----------------------
  us-central | {us-central-a,us-central-b} | {movr}               | {}
  us-east    | {us-east-a,us-east-b}       | {cockroachlabs,movr} | {cockroachlabs,movr}
  us-west    | {us-west-a,us-west-b}       | {cockroachlabs,movr} | {}
(3 rows)
```

To show the region information for each database in the cluster, use `SHOW REGIONS FROM ALL DATABASES`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW REGIONS FROM ALL DATABASES;
```

```
  database_name |           regions            | primary_region
----------------+------------------------------+-----------------
  cockroachlabs | {us-east,us-west}            | us-east
  defaultdb     | {}                           | NULL
  movr          | {us-central,us-east,us-west} | us-east
  postgres      | {}                           | NULL
  system        | {}                           | NULL
(5 rows)
```

## See also

* [Multi-Region Capabilities Overview](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md)
* [`ADD REGION`](https://docs.cockroachlabs.com/docs/stable/alter-database#add-region)
* [`DROP REGION`](https://docs.cockroachlabs.com/docs/stable/alter-database#drop-region)
* [`ADD SUPER REGION`](https://docs.cockroachlabs.com/docs/stable/alter-database#add-super-region)
* [`ALTER SUPER REGION`](alter-database#alter-super-region)
* [`DROP SUPER REGION`](https://docs.cockroachlabs.com/docs/stable/alter-database#drop-super-region)
* [`SHOW SUPER REGIONS`](https://docs.cockroachlabs.com/docs/stable/show-super-regions)
* [Secondary regions](https://docs.cockroachlabs.com/docs/stable/multiregion-overview.md#secondary-regions)
* [`SET SECONDARY REGION`](https://docs.cockroachlabs.com/docs/stable/alter-database#set-secondary-region)
* [`DROP SECONDARY REGION`](https://docs.cockroachlabs.com/docs/stable/alter-database#drop-secondary-region)
* [SQL Statements](https://docs.cockroachlabs.com/docs/stable/sql-statements.md)