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. |
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 .
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:
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)
\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;
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;
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)
> COMMENT ON TABLE users IS 'This table contains information about users.';
> 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)
> 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)
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/v25.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/v25.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/v25.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/v25.3/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.
-
on
movr to us-east:
> ALTER DATABASE movr SET PRIMARY REGION "us-east";
-
Configure the
users table to be :
> ALTER TABLE users SET LOCALITY REGIONAL BY ROW;
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
