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.
Estimating the number of rows in a table costs resources. To improve the performance of SHOW TABLES queries, disable row-count estimation by setting the sql.show_tables.estimated_row_count.enabled cluster setting to false before executing a SHOW TABLES statement.
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;
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:
> \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:
> 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 | 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.
> 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 | 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.
> 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 | 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:
> 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;
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/v20.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/v20.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/v20.2/information-schema.html#check_constraints
| | | | https://www.postgresql.org/docs/9.5/infoschema-check-constraints.html
...
(23 rows)
Yes
No