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.enabled
cluster setting tofalse
before executing aSHOW 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
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 "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 | 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/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 | NULL | 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 | 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/v21.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/v21.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.
This is an enterprise-only feature. Request a 30-day trial license to try it out.
First, set the primary region on movr
to us-east
:
> ALTER DATABASE movr SET PRIMARY REGION "us-east";
All tables will be REGIONAL BY TABLE
in the primary region by default.
Next, configure the users
table to be REGIONAL 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)
Locality information for tables is also available in the locality
column within the crdb_internal.tables
table.