SHOW TABLES

The SHOW TABLES statement lists the schema, table name, table type, owner, and estimated row count for 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

SHOW TABLES FROM database_name . schema_name WITH COMMENT

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.

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 cluster setting 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 MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr dataset preloaded:

icon/buttons/copy
$ cockroach demo

Show tables in the current database

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

icon/buttons/copy
> SHOW TABLES;
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | promo_codes                | table |                1000
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | users                      | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(6 rows)

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

icon/buttons/copy
> \dt
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | promo_codes                | table |                1000
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | users                      | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(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:

icon/buttons/copy
> SHOW TABLES FROM movr.information_schema;
icon/buttons/copy
> SHOW TABLES FROM information_schema;

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

     schema_name     |            table_name             | type  | estimated_row_count
---------------------+-----------------------------------+-------+----------------------
  information_schema | administrable_role_authorizations | table |                NULL
  information_schema | applicable_roles                  | table |                NULL
  information_schema | check_constraints                 | table |                NULL
  information_schema | column_privileges                 | table |                NULL
  ...
(23 rows)

Show tables in a different database

You can also show tables from a different database.

icon/buttons/copy
> SHOW TABLES FROM system.public;
icon/buttons/copy
> SHOW TABLES FROM system;

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

  schema_name |           table_name            | type  | estimated_row_count
--------------+---------------------------------+-------+----------------------
  public      | comments                        | table |                NULL
  public      | descriptor                      | table |                NULL
  public      | eventlog                        | table |                NULL
  public      | jobs                            | table |                NULL
  ...
(29 rows)

Show user-defined tables with comments

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

icon/buttons/copy
> COMMENT ON TABLE users IS 'This table contains information about users.';

To view a table's comments:

icon/buttons/copy
> SHOW TABLES FROM movr WITH COMMENT;
  schema_name |         table_name         | type  | estimated_row_count |                   comment
--------------+----------------------------+-------+---------------------+-----------------------------------------------
  public      | promo_codes                | table |                1000 |
  public      | rides                      | table |                 500 |
  public      | user_promo_codes           | table |                   0 |
  public      | users                      | table |                  50 | This table contains information about users.
  public      | vehicle_location_histories | table |                1000 |
  public      | vehicles                   | table |                  15 |
(6 rows)

You can also view comments on a table with SHOW CREATE:

icon/buttons/copy
> 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:

icon/buttons/copy
> SHOW TABLES FROM information_schema WITH COMMENT;
     schema_name     |            table_name             | type  | estimated_row_count |                                                              comment
---------------------+-----------------------------------+-------+---------------------+-------------------------------------------------------------------------------------------------------------------------------------
  information_schema | administrable_role_authorizations | table |                NULL | roles for which the current user has admin option
                     |                                   |       |                     | https://www.cockroachlabs.com/docs/v21.2/information-schema.html#administrable_role_authorizations
                     |                                   |       |                     | https://www.postgresql.org/docs/9.5/infoschema-administrable-role-authorizations.html
  information_schema | applicable_roles                  | table |                NULL | roles available to the current user
                     |                                   |       |                     | https://www.cockroachlabs.com/docs/v21.2/information-schema.html#applicable_roles
                     |                                   |       |                     | https://www.postgresql.org/docs/9.5/infoschema-applicable-roles.html
  information_schema | check_constraints                 | table |                NULL | check constraints
                     |                                   |       |                     | https://www.cockroachlabs.com/docs/v21.2/information-schema.html#check_constraints
                     |                                   |       |                     | https://www.postgresql.org/docs/9.5/infoschema-check-constraints.html
...
(23 rows)

See also

YesYes NoNo