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

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 TABLES` <InternalLink path="sql-statements">statement</InternalLink> lists the schema, table name, table type, owner, and estimated row count for the tables or <InternalLink path="views">views</InternalLink> in a schema or database.

<Note>
  While a table or view is being <InternalLink path="drop-table">dropped</InternalLink>, `SHOW TABLES` will list the object with a `(dropped)` suffix.
</Note>

## Synopsis

<img src="https://mintcdn.com/cockroachlabs/URZZsrJ2y-tKyo7i/images/sql-diagrams/v24.3/show_tables.svg?fit=max&auto=format&n=URZZsrJ2y-tKyo7i&q=85&s=cca012582660691c941a8d3e6056763b" alt="show_tables syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="689" height="187" data-path="images/sql-diagrams/v24.3/show_tables.svg" />

## Required privileges

The `CONNECT` <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink> on the database of the concerned table is required to list it with SHOW TABLES.

## Parameters

| Parameter        | Description                                        |
| ---------------- | -------------------------------------------------- |
| `database\_name` | The name of the database for which to show tables. |
| `schema\_name`   | The name of the schema for which to show tables.   |

When a `database_name` and `schema_name` are omitted, the tables of the <InternalLink path="sql-name-resolution#current-schema">current schema</InternalLink> in the <InternalLink path="sql-name-resolution#current-database">current database</InternalLink> are listed.

`SHOW TABLES` will attempt to find a schema with the specified name first. If that fails, it will try to find a database with that name instead, and list the tables of its `public` schema. For more details, see <InternalLink path="sql-name-resolution">Name Resolution</InternalLink>.

## Performance

To optimize the performance of the `SHOW TABLES` statement, you can do the following:

* Disable table row-count estimation by setting the `sql.show_tables.estimated_row_count.enabled` <InternalLink path="cluster-settings">cluster setting</InternalLink> to `false` before executing a `SHOW TABLES` statement.
* Avoid running `SHOW TABLES` on databases with a large number of tables (e.g., more than 10,000 tables).

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

### Show tables in the current database

`SHOW TABLES` uses the <InternalLink path="sql-name-resolution#current-schema">current schema</InternalLink> `public` set by default in `search_path`:

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  schema_name |         table_name         | type  | owner | estimated_row_count | locality
--------------+----------------------------+-------+-------+---------------------+-----------
  public      | promo_codes                | table | demo  |                   0 | NULL
  public      | rides                      | table | demo  |                   0 | NULL
  public      | user_promo_codes           | table | demo  |                   0 | NULL
  public      | users                      | table | demo  |                   0 | NULL
  public      | vehicle_location_histories | table | demo  |                   0 | NULL
  public      | vehicles                   | table | demo  |                   0 | NULL
(6 rows)
```

Alternatively, within the built-in SQL shell, you can use the `\dt` <InternalLink path="cockroach-sql#commands">shell command</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> \dt
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  schema_name |         table_name         | type  | owner | estimated_row_count | locality
--------------+----------------------------+-------+-------+---------------------+-----------
  public      | promo_codes                | table | demo  |                   0 | NULL
  public      | rides                      | table | demo  |                   0 | NULL
  public      | user_promo_codes           | table | demo  |                   0 | NULL
  public      | users                      | table | demo  |                   0 | NULL
  public      | vehicle_location_histories | table | demo  |                   0 | NULL
  public      | vehicles                   | table | demo  |                   0 | NULL
(6 rows)
```

### Show tables in a different schema

You can show the tables in schemas other than the current schema. You can also show the schema by table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TABLES FROM movr.information_schema;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TABLES FROM information_schema;
```

Because `movr` is the current database, these statements return the same output:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     schema_name     |              table_name               | type  | owner | estimated_row_count | locality
---------------------+---------------------------------------+-------+-------+---------------------+-----------
  information_schema | administrable_role_authorizations     | table | NULL  |                NULL | NULL
  information_schema | applicable_roles                      | table | NULL  |                NULL | NULL
  information_schema | attributes                            | table | NULL  |                NULL | NULL
  information_schema | character_sets                        | table | NULL  |                NULL | NULL
  ...
(86 rows)
```

### Show tables in a different database

You can also show tables from a different database.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TABLES FROM system.public;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TABLES FROM system;
```

Because `public` is the current schema, these statements return the same output:

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  schema_name |           table_name            | type  | owner | estimated_row_count | locality
--------------+---------------------------------+-------+-------+---------------------+-----------
  public      | comments                        | table | NULL  |                   0 | NULL
  public      | database_role_settings          | table | NULL  |                   0 | NULL
  public      | descriptor                      | table | NULL  |                   0 | NULL
  public      | eventlog                        | table | NULL  |                   0 | NULL
  ...
(36 rows)
```

### Show user-defined tables with comments

You can use <InternalLink path="comment-on">`COMMENT ON`</InternalLink> to add comments on a table.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> COMMENT ON TABLE users IS 'This table contains information about users.';
```

To view a table's comments:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TABLES FROM movr WITH COMMENT;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  schema_name |         table_name         | type  | owner | estimated_row_count | locality |                   comment
--------------+----------------------------+-------+-------+---------------------+----------+-----------------------------------------------
  public      | promo_codes                | table | demo  |                1000 | NULL     |
  public      | rides                      | table | demo  |                 500 | NULL     |
  public      | user_promo_codes           | table | demo  |                   0 | NULL     |
  public      | users                      | table | demo  |                  50 | NULL     | This table contains information about users.
  public      | vehicle_location_histories | table | demo  |                1000 | NULL     |
  public      | vehicles                   | table | demo  |                  15 | NULL     |
(6 rows)
```

You can also view comments on a table with <InternalLink path="show-create">`SHOW CREATE`</InternalLink>:

```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 users_pkey PRIMARY KEY (city ASC, id ASC)
             | );
             | COMMENT ON TABLE users IS 'This table contains information about users.'
(1 row)
```

For more information, see <InternalLink path="comment-on">`COMMENT ON`</InternalLink>.

### Show virtual tables with comments

The virtual tables in the `pg_catalog`, `information_schema`, and `crdb_internal` schemas contain useful comments, often with links to further documentation.

To view virtual tables with comments and documentation links, use `SHOW TABLES FROM <virtual schema> WITH COMMENT`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TABLES FROM information_schema WITH COMMENT;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     schema_name     |              table_name               | type  | owner | estimated_row_count | locality |                                                              comment
---------------------+---------------------------------------+-------+-------+---------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------
  information_schema | administrable_role_authorizations     | table | NULL  |                NULL | NULL     | roles for which the current user has admin option
                     |                                       |       |       |                     |          | https://www.cockroachlabs.com/docs/v24.3/information-schema.html#administrable_role_authorizations
                     |                                       |       |       |                     |          | https://www.postgresql.org/docs/9.5/infoschema-administrable-role-authorizations.html
  information_schema | applicable_roles                      | table | NULL  |                NULL | NULL     | roles available to the current user
                     |                                       |       |       |                     |          | https://www.cockroachlabs.com/docs/v24.3/information-schema.html#applicable_roles
                     |                                       |       |       |                     |          | https://www.postgresql.org/docs/9.5/infoschema-applicable-roles.html
  information_schema | attributes                            | table | NULL  |                NULL | NULL     | attributes was created for compatibility and is currently unimplemented
  information_schema | character_sets                        | table | NULL  |                NULL | NULL     | character sets available in the current database
                     |                                       |       |       |                     |          | https://www.cockroachlabs.com/docs/v24.3/information-schema.html#character_sets
                     |                                       |       |       |                     |          | https://www.postgresql.org/docs/9.5/infoschema-character-sets.html
  information_schema | check_constraint_routine_usage        | table | NULL  |                NULL | NULL     | check_constraint_routine_usage was created for compatibility and is currently unimplemented
  information_schema | check_constraints                     | table | NULL  |                NULL | NULL     | check constraints
                     |                                       |       |       |                     |          | https://www.cockroachlabs.com/docs/v24.3/information-schema.html#check_constraints
                     |                                       |       |       |                     |          | https://www.postgresql.org/docs/9.5/infoschema-check-constraints.html
  ...
(86 rows)
```

### Show locality of tables

For <InternalLink path="multiregion-overview">multi-region</InternalLink> tables, you can display the locality of each table using the `SHOW TABLES` command.

1. <InternalLink path="alter-database#set-primary-region">Set the primary region</InternalLink> on `movr` to `us-east`:

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

   All tables will be <InternalLink path="alter-table#set-the-table-locality-to-regional-by-table">`REGIONAL BY TABLE`</InternalLink> in the primary region by default.
2. Configure the `users` table to be <InternalLink path="alter-table#set-the-table-locality-to-regional-by-row">`REGIONAL BY ROW`</InternalLink>:

   ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
   > ALTER TABLE users SET LOCALITY REGIONAL BY ROW;
   ```

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

   ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     schema_name |         table_name         | type  | owner | estimated_row_count |              locality
   --------------+----------------------------+-------+-------+---------------------+--------------------------------------
     public      | promo_codes                | table | demo  |                1000 | REGIONAL BY TABLE IN PRIMARY REGION
     public      | rides                      | table | demo  |                 500 | REGIONAL BY TABLE IN PRIMARY REGION
     public      | user_promo_codes           | table | demo  |                   0 | REGIONAL BY TABLE IN PRIMARY REGION
     public      | users                      | table | demo  |                  50 | REGIONAL BY ROW
     public      | vehicle_location_histories | table | demo  |                1000 | REGIONAL BY TABLE IN PRIMARY REGION
     public      | vehicles                   | table | demo  |                  15 | REGIONAL BY TABLE IN PRIMARY REGION
   (6 rows)
   ```

<Note>
  Locality information for tables is also available in the `locality` column within the <InternalLink path="crdb-internal">`crdb_internal.tables`</InternalLink> table.
</Note>

## See also

* <InternalLink path="show-databases">`SHOW DATABASES`</InternalLink>
* <InternalLink path="show-schemas">`SHOW SCHEMAS`</InternalLink>
* <InternalLink path="create-table">`CREATE TABLE`</InternalLink>
* <InternalLink path="create-view">`CREATE VIEW`</InternalLink>
* <InternalLink path="comment-on">`COMMENT ON`</InternalLink>
* <InternalLink path="information-schema">Information Schema</InternalLink>