> ## 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 REGIONS

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 <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `SHOW REGIONS` <InternalLink path="sql-statements">statement</InternalLink> lists the <InternalLink path="multiregion-overview#cluster-regions">cluster regions</InternalLink> for a multi-region cluster, or the <InternalLink path="multiregion-overview#database-regions">database regions</InternalLink> for the databases in a multi-region cluster.

## Synopsis

<img src="https://mintcdn.com/cockroachlabs/2sY29RlbaeZjvP07/images/sql-diagrams/v25.4/show_regions.svg?fit=max&auto=format&n=2sY29RlbaeZjvP07&q=85&s=3131dbcfd17803500520163d3018843e" alt="show_regions syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="723" height="233" data-path="images/sql-diagrams/v25.4/show_regions.svg" />

## Required privileges

Only members of the <InternalLink path="security-reference/authorization#admin-role">`admin` role</InternalLink> can run `SHOW REGIONS`. By default, the `root` user belongs to the `admin` role.

## Parameters

| Parameter                      | Description                                                                                                                                                                                                                                                                                                                    |
| ------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| `FROM CLUSTER`                 | Show the <InternalLink path="multiregion-overview#cluster-regions">cluster regions</InternalLink> for a cluster. Cluster regions are specified <InternalLink path="cockroach-start#locality">at cluster startup</InternalLink>.                                                                                                |
| `FROM DATABASE`                | Show all <InternalLink path="multiregion-overview#database-regions">database regions</InternalLink> for the current database. Database regions can be added <InternalLink path="create-database">at database creation</InternalLink>, or <InternalLink path="alter-database">after a database has been created</InternalLink>. |
| `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 <InternalLink path="alter-database#set-primary-region">primary region</InternalLink>.        | ✓              |                             |                              |
| `secondary\_region\_of` | A set of database names for which the region is the <InternalLink path="multiregion-overview#secondary-regions">secondary region</InternalLink>. | ✓              |                             |                              |
| `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 <InternalLink path="multiregion-overview#secondary-regions">secondary region</InternalLink> 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 <InternalLink path="movr">MovR: A Global Vehicle-sharing App</InternalLink>.

To follow along, run <InternalLink path="cockroach-demo">`cockroach demo`</InternalLink> with the <InternalLink path="cockroach-demo#flags">`--nodes`</InternalLink> and <InternalLink path="cockroach-demo#flags">`--demo-locality`</InternalLink> 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 <InternalLink path="sql-name-resolution#current-database">current database</InternalLink>.

```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;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    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.

<InternalLink path="alter-database#add-region">Add an available region</InternalLink> as the primary region for the `movr` database:

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE PRIMARY REGION
```

<Note>
  Only <InternalLink path="multiregion-overview#cluster-regions">cluster regions</InternalLink> (i.e., regions that are defined at <InternalLink path="cockroach-start#locality">node startup time</InternalLink>) can be added to a multi-region database.
</Note>

Then, add more regions to the database:

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER DATABASE ADD REGION
```

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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 <InternalLink path="multiregion-overview#secondary-regions">Secondary regions</InternalLink>.

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;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    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;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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

* <InternalLink path="multiregion-overview">Multi-Region Capabilities Overview</InternalLink>
* <InternalLink path="alter-database#add-region">`ADD REGION`</InternalLink>
* <InternalLink path="alter-database#drop-region">`DROP REGION`</InternalLink>
* <InternalLink path="alter-database#add-super-region">`ADD SUPER REGION`</InternalLink>
* <InternalLink path="alter-database#drop-super-region">`DROP SUPER REGION`</InternalLink>
* <InternalLink path="show-super-regions">`SHOW SUPER REGIONS`</InternalLink>
* <InternalLink path="multiregion-overview#secondary-regions">Secondary regions</InternalLink>
* <InternalLink path="alter-database#set-secondary-region">`SET SECONDARY REGION`</InternalLink>
* <InternalLink path="alter-database#drop-secondary-region">`DROP SECONDARY REGION`</InternalLink>
* <InternalLink path="sql-statements">SQL Statements</InternalLink>