SHOW TABLES

Description

This statement is used to display all tables and views under the current db.

Syntax

SHOW [ FULL ] TABLES [ FROM [ <catalog_name>.]<db_name> ][ LIKE <like_condition> ]

Optional parameters

1. FULL

If this parameter is added to the statement, the returned result will have three more columns, namely Table_type (table type), Storage_format (storage format), and Inverted_index_storage_format (inverted index storage format).

2. FROM [ <catalog_name>.]<db_name>

In the FROM clause, you can specify the catalog name and database name to be queried.

2. LIKE <like_condition>

In the LIKE clause, you can perform fuzzy queries based on table names.

Return value

Column name (Column)	Type (DataType)	Notes (Notes)
Tables_in_<db_name>	String	All tables and views under the database where <db_name> is located.
Table_type	String	Table and view type.
Storage_format	String	Table and view storage format.
Inverted_index_storage_format	String	Table and view inverted index storage format.

Access Control Requirements

The user who executes this SQL command must have at least the following permissions:

Privilege (Privilege)	Object (Object)	Notes (Notes)
SELECT_PRIV	Table (Table), View (View)	Only tables and views with query permissions can be displayed.

Usage Notes

• If the FROM clause is not specified in the statement, the corresponding database needs to be used for execution.

Examples

• View all tables under DB

  SHOW TABLES;

  +---------------------------------+
  | Tables_in_demo                  |
  +---------------------------------+
  | ads_client_biz_aggr_di_20220419 |
  | cmy1                            |
  | cmy2                            |
  | intern_theme                    |
  | left_table                      |
  +---------------------------------+

• Fuzzy query by table name

  SHOW TABLES LIKE '%cm%'

  +----------------+
  | Tables_in_demo |
  +----------------+
  | cmy1           |
  | cmy2           |
  +----------------+

• Use FULL to query the tables and views under db

  SHOW FULL TABLES

  +----------------+------------+----------------+-------------------------------+
  | Tables_in_demo | Table_type | Storage_format | Inverted_index_storage_format |
  +----------------+------------+----------------+-------------------------------+
  | test_table     | BASE TABLE | V2             | V1                            |
  | test_view      | VIEW       | NONE           | NONE                          |
  +----------------+------------+----------------+-------------------------------+