SHOW TABLES

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

SHOWTABLESFROMdatabase_name.schema_nameWITHCOMMENT

Required privileges

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

Parameters

ParameterDescription
database_nameThe name of the database for which to show tables.
schema_nameThe 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

These example assumes that the bank database has been set as the current database for the session, either via the SET statement or in the client's connection string.

Show tables in the current database

  1. > SHOW TABLES;
  1. +---------------+
  2. | table_name    |
  3. +---------------+
  4. | accounts      |
  5. | user_accounts |
  6. +---------------+
  7. (2 rows)

This uses the current schema public set by default in search_path.

Show tables in a different schema

  1. > SHOW TABLES FROM information_schema;
  1. > SHOW TABLES FROM bank.information_schema; -- also possible
  1. +-----------------------------------+
  2. |            table_name             |
  3. +-----------------------------------+
  4. | administrable_role_authorizations |
  5. | applicable_roles                  |
  6. | column_privileges                 |
  7. | columns                           |
  8. | constraint_column_usage           |
  9. | enabled_roles                     |
  10. | key_column_usage                  |
  11. | parameters                        |
  12. | referential_constraints           |
  13. | role_table_grants                 |
  14. | routines                          |
  15. | schema_privileges                 |
  16. | schemata                          |
  17. | sequences                         |
  18. | statistics                        |
  19. | table_constraints                 |
  20. | table_privileges                  |
  21. | tables                            |
  22. | user_privileges                   |
  23. | views                             |
  24. +-----------------------------------+
  25. (20 rows)

Show tables in a different database

  1. > SHOW TABLES FROM startrek.public;
  1. > SHOW TABLES FROM startrek; -- also possible
  1. +-------------------+
  2. | table_name        |
  3. +-------------------+
  4. | episodes          |
  5. | quotes            |
  6. | quotes_per_season |
  7. +-------------------+
  8. (3 rows)

Show tables with comments

New in v19.1:You can use COMMENT ON to add comments on a table. To view a table's comments:

  1. > SHOW TABLES FROM customers WITH COMMENT;
  1.   table_name |         comment
  2. +------------+--------------------------+
  3.   dogs       | This is a sample comment
  4. (1 row)

For more information, see COMMENT ON.

See also

Was this page helpful?
YesNo