SHOW TABLES

The SHOW TABLES lists the schema, table name, table type, owner, and estimated row count for the tables or in a schema or database.

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

Synopsis

Required privileges

The CONNECT 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 in the 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 .

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 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 .

To follow along, run with the and 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 .

$ 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 public set by default in search_path:

> SHOW TABLES;

  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 :

> \dt

  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:

> SHOW TABLES FROM movr.information_schema;

> SHOW TABLES FROM information_schema;

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

     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.

> SHOW TABLES FROM system.public;

> SHOW TABLES FROM system;

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

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

  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 :

> 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 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 .

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;

     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/v26.1/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/v26.1/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/v26.1/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/v26.1/information-schema.html#check_constraints
                     |                                       |       |       |                     |          | https://www.postgresql.org/docs/9.5/infoschema-check-constraints.html
  ...
(86 rows)

Show locality of tables

For tables, you can display the locality of each table using the SHOW TABLES command.

1. on movr to us-east:

   > ALTER DATABASE movr SET PRIMARY REGION "us-east";
   

   All tables will be in the primary region by default.

2. Configure the users table to be :

   > ALTER TABLE users SET LOCALITY REGIONAL BY ROW;
   
   > SHOW TABLES;
   
     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)
   

Locality information for tables is also available in the locality column within the table.

See also