The SHOW TABLES statement lists 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 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;
table_name
+----------------------------+
promo_codes
rides
user_promo_codes
users
vehicle_location_histories
vehicles
(6 rows)
Alternatively, within the built-in SQL shell, you can use the \dt shell command:
> \dt
table_name
+----------------------------+
promo_codes
rides
user_promo_codes
users
vehicle_location_histories
vehicles
(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:
table_name
+-----------------------------------+
administrable_role_authorizations
applicable_roles
column_privileges
columns
constraint_column_usage
enabled_roles
key_column_usage
parameters
referential_constraints
role_table_grants
routines
schema_privileges
schemata
sequences
statistics
table_constraints
table_privileges
tables
user_privileges
views
(20 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:
table_name
+------------------+
comments
descriptor
eventlog
jobs
lease
locations
namespace
rangelog
role_members
settings
table_statistics
ui
users
web_sessions
zones
(15 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;
table_name | comment
+----------------------------+----------------------------------------------+
users | This table contains information about users.
vehicles |
rides |
vehicle_location_histories |
promo_codes |
user_promo_codes |
(6 rows)
New in v20.1: 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;
table_name | comment
+-----------------------------------+------------------------------------------------------------------------------------------------------------------------------------+
administrable_role_authorizations | roles for which the current user has admin option
| https://www.cockroachlabs.com/docs/v19.2/information-schema.html#administrable_role_authorizations
| https://www.postgresql.org/docs/9.5/infoschema-administrable-role-authorizations.html
applicable_roles | roles available to the current user
| https://www.cockroachlabs.com/docs/v19.2/information-schema.html#applicable_roles
| https://www.postgresql.org/docs/9.5/infoschema-applicable-roles.html
check_constraints | check constraints
| https://www.cockroachlabs.com/docs/v19.2/information-schema.html#check_constraints
| https://www.postgresql.org/docs/9.5/infoschema-check-constraints.html
column_privileges | column privilege grants (incomplete)
| https://www.cockroachlabs.com/docs/v19.2/information-schema.html#column_privileges
| https://www.postgresql.org/docs/9.5/infoschema-column-privileges.html~~~
...
