# SHOW TABLES

The `SHOW TABLES` statement lists the tables or views in a schema or database.

Note:

While a table or view is being dropped, `SHOW TABLES` will list the object with a `(dropped)` suffix.

## Synopsis

## Required privileges

The `SELECT` privilege on a 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 current schema in the current database 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 Name Resolution.

## 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` to start a temporary, in-memory cluster with the `movr` dataset preloaded:

    $ cockroach demo

### Show tables in the current database

`SHOW TABLES` uses the current schema `public` set by default in `search_path`:

    > SHOW TABLES;

              table_name
    +----------------------------+
      promo_codes
      rides
      user_promo_codes
      users
      vehicle_location_histories
      vehicles
    (6 rows)

Alternatively, within the built-in SQL shell, you can use the `\dt` shell command:

    > \dt

              table_name
    +----------------------------+
      promo_codes
      rides
      user_promo_codes
      users
      vehicle_location_histories
      vehicles
    (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:

    > SHOW TABLES FROM movr.information_schema;

    > SHOW TABLES FROM information_schema;

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

                 table_name
    +-----------------------------------+
      administrable_role_authorizations
      applicable_roles
      column_privileges
      columns
      constraint_column_usage
      enabled_roles
      key_column_usage
      parameters
      referential_constraints
      role_table_grants
      routines
      schema_privileges
      schemata
      sequences
      statistics
      table_constraints
      table_privileges
      tables
      user_privileges
      views
    (20 rows)

### Show tables in a different database

You can also show tables from a different database.

    > SHOW TABLES FROM system.public;

    > SHOW TABLES FROM system;

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

         table_name
    +------------------+
      comments
      descriptor
      eventlog
      jobs
      lease
      locations
      namespace
      rangelog
      role_members
      settings
      table_statistics
      ui
      users
      web_sessions
      zones
    (15 rows)

### Show user-defined tables with comments

You can use `COMMENT ON` to add comments on a table.

    > COMMENT ON TABLE users IS 'This table contains information about users.';

To view a table's comments:

    > SHOW TABLES FROM movr WITH COMMENT;

              table_name         |                   comment
    +----------------------------+----------------------------------------------+
      users                      | This table contains information about users.
      vehicles                   |
      rides                      |
      vehicle_location_histories |
      promo_codes                |
      user_promo_codes           |
    (6 rows)

New in v20.1: You can also view comments on a table with `SHOW CREATE`:

    > 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)
                 | );
                 | COMMENT ON TABLE users IS 'This table contains information about users.'
    (1 row)

For more information, see `COMMENT ON`.

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

    > SHOW TABLES FROM information_schema WITH COMMENT;

                 table_name             |                                                              comment
    +-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------+
      administrable_role_authorizations | roles for which the current user has admin option
                                        | https://www.cockroachlabs.com/docs/v20.1/information-schema.html#administrable_role_authorizations
                                        | https://www.postgresql.org/docs/9.5/infoschema-administrable-role-authorizations.html
      applicable_roles                  | roles available to the current user
                                        | https://www.cockroachlabs.com/docs/v20.1/information-schema.html#applicable_roles
                                        | https://www.postgresql.org/docs/9.5/infoschema-applicable-roles.html
      check_constraints                 | check constraints
                                        | https://www.cockroachlabs.com/docs/v20.1/information-schema.html#check_constraints
                                        | https://www.postgresql.org/docs/9.5/infoschema-check-constraints.html
      column_privileges                 | column privilege grants (incomplete)
                                        | https://www.cockroachlabs.com/docs/v20.1/information-schema.html#column_privileges
                                        | https://www.postgresql.org/docs/9.5/infoschema-column-privileges.html~~~
    ...

## See also

- `SHOW DATABASES`
- `SHOW SCHEMAS`
- `CREATE TABLE`
- `CREATE VIEW`
- `COMMENT ON`
- Information Schema