Skip to main content

SHOW TABLES

Lists the tables in the current or a specified database.

Syntax

SHOW [FULL] TABLES 
[{FROM | IN} <database_name>]
[HISTORY]
[LIKE '<pattern>' | WHERE <expr>]

Where:

[FULL]: Lists the tables with their property information. See Examples for more details.

[{FROM | IN} <database_name>]: Specifies a database. If omitted, the command returns the results from the current database.

[HISTORY]: If present, the results will include the dropped tables that are still within their retention period (24 hours by default).

[LIKE '<pattern>']: Filters the results by the table names using pattern matching.

[WHERE <expr>]: Filters the results using an expression in the WHERE clause.

Examples

The following example lists all the tables in the current database:

SHOW TABLES;

---
| tables_in_default |
|-------------------|
| hr_team |
| members |
| members_previous |
| members_view |
| support_team |
| t1 |

The following example lists all the tables with their properties information:

SHOW FULL TABLES;

---
| tables_in_default | table_type | table_catalog | engine | create_time | num_rows | data_size | data_compressed_size | index_size |
|-------------------|------------|---------------|--------|-------------------------------|----------|-----------|----------------------|------------|
| hr_team | BASE TABLE | default | FUSE | 2022-08-29 12:58:09.992 +0000 | 4 | 80 | 674 | 774 |
| members | BASE TABLE | default | FUSE | 2022-08-29 17:53:34.282 +0000 | 2 | 38 | 392 | 460 |
| members_previous | BASE TABLE | default | FUSE | 2022-08-29 18:20:57.599 +0000 | 0 | 0 | 0 | 0 |
| members_view | BASE TABLE | default | VIEW | 2022-09-02 18:24:04.658 +0000 | \N | \N | \N | \N |
| support_team | BASE TABLE | default | FUSE | 2022-08-29 12:57:45.469 +0000 | 3 | 57 | 350 | 387 |
| t1 | BASE TABLE | default | FUSE | 2022-08-23 18:08:40.158 +0000 | 0 | 0 | 0 | 0 |

The following example demonstrates that the results will include dropped tables when the option HISTORY is present:

DROP TABLE t1;

SHOW TABLES;

---
| tables_in_default |
|-------------------|
| hr_team |
| members |
| members_previous |
| members_view |
| support_team |

SHOW TABLES HISTORY;

---
| tables_in_default | drop_time |
|-------------------|-------------------------------|
| hr_team | NULL |
| members | NULL |
| members_previous | NULL |
| members_view | NULL |
| support_team | NULL |
| t1 | 2022-09-02 18:29:27.918 +0000 |

The following example lists the tables containing the string "team" at the end of their name:

SHOW TABLES LIKE '%team';

---
| tables_in_default |
|-------------------|
| hr_team |
| support_team |
tip

Please note that the pattern matching is CASE-SENSITIVE. No results will be returned if you code the previous statement like this:

SHOW TABLES LIKE '%TEAM';

The following example lists the views in the current database using a WHERE clause:

SHOW TABLES WHERE engine = 'VIEW';

---
| tables_in_default |
|-------------------|
| members_view |