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.
While a table or view is being dropped, SHOW TABLES will list the object with a (dropped) suffix.
Synopsis
Required privileges
The CONNECT privilege 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 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.enabledcluster setting tofalsebefore executing aSHOW TABLESstatement. - Avoid running
SHOW TABLESon 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 with the --nodes and --demo-locality 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 current database.
$ 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 current schema 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 shell command:
> \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 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;
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:
> 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 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;
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.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 | NULL | NULL | roles available to the current user
| | | | | | https://www.cockroachlabs.com/docs/v24.2/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.2/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.2/information-schema.html#check_constraints
| | | | | | https://www.postgresql.org/docs/9.5/infoschema-check-constraints.html
...
(86 rows)
Show locality of tables
For multi-region tables, you can display the locality of each table using the SHOW TABLES command.
Set the primary region on
movrtous-east:> ALTER DATABASE movr SET PRIMARY REGION "us-east";All tables will be
REGIONAL BY TABLEin the primary region by default.Configure the
userstable to beREGIONAL BY ROW:> 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)Note:Locality information for tables is also available in the
localitycolumn within thecrdb_internal.tablestable.
