The SHOW TABLES statement lists the tables or views in a schema or database.

Note:

While a table or view is being dropped, SHOW TABLES will list the object with a (dropped) suffix.

Synopsis

SHOW TABLES FROM database_name . schema_name WITH COMMENT

Required privileges

No privileges are required to list the tables in a schema or database.

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:

copy
icon/buttons/copy
$ cockroach demo

Show tables in the current database

SHOW TABLES uses the current schema public set by default in search_path:

copy
icon/buttons/copy
> SHOW TABLES;
          table_name
+----------------------------+
  promo_codes
  rides
  user_promo_codes
  users
  vehicle_location_histories
  vehicles
(6 rows)

New in v19.2: Alternatively, within the built-in SQL shell, you can use the \dt shell command:

copy
icon/buttons/copy
> \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:

copy
icon/buttons/copy
> SHOW TABLES FROM movr.information_schema;
copy
icon/buttons/copy
> 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.

copy
icon/buttons/copy
> SHOW TABLES FROM system.public;
copy
icon/buttons/copy
> 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 tables with comments

You can use COMMENT ON to add comments on a table.

copy
icon/buttons/copy
> COMMENT ON TABLE users IS 'This table contains information about users.';

To view a table's comments:

copy
icon/buttons/copy
> 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)

For more information, see COMMENT ON.

See also



Yes No