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~~~
...