SHOW TABLES

Shows the list of tables in the currently selected database, or in another database if db_name is specified.

Syntax

SHOW [FULL] [TEMPORARY] TABLES [{FROM | IN} db_name]
[[EXTENDED] LIKE pattern | WHERE TABLE_TYPE {= | !=} {'VIEW' | 'BASE TABLE'}]

Remarks

  • db_name is the name of a SingleStore database. If db_name is not specified, then a database must be currently selected (see USE ).

  • The SHOW TABLES command (without the TEMPORARY option) run on the child or master aggregator displays the global temporary tables and normal tables, but not the temporary tables.

  • SHOW TEMPORARY TABLES will show temporary tables but not global temporary tables.

  • If you specify a table type, you must also use the FULL keyword.

  • This command can be run on any SingleStore node (see Node Requirements for SingleStore Commands).

  • See the Permission Matrix for the required permission.

Examples

Example 1

Suppose a global temporary table globtemp and a temporary table temptab exists in the database, along with few normal tables. Run the following commands on the master aggregator to view the tables:

SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| allviews      |
| circle        |
| globtemp      |
| product       |
| reading       |
| source        |
| stock         |
| stockN        |
+---------------+
SHOW TEMPORARY TABLES;
+---------------+------------+------------+
| Tables_in_db1 | aggregator | connection |
+---------------+------------+------------+
| temptab       |          1 |          5 |
+---------------+------------+------------+

Example 2

SHOW TABLES EXTENDED;
+---------------+------------------------+-------------+-------------------+
| Tables_in_db1 | Table_type             | distributed | Storage_type      |
+---------------+------------------------+-------------+-------------------+
| allviews      | BASE TABLE             |           1 | INMEMORY_ROWSTORE |
| circle        | BASE TABLE             |           1 | INMEMORY_ROWSTORE |
| globtemp      | GLOBAL TEMPORARY TABLE |           1 | INMEMORY_ROWSTORE |
| product       | BASE TABLE             |           1 | INMEMORY_ROWSTORE |
| reading       | BASE TABLE             |           1 | INMEMORY_ROWSTORE |
| source        | BASE TABLE             |           1 | INMEMORY_ROWSTORE |
| stock         | BASE TABLE             |           1 | INMEMORY_ROWSTORE |
| stockN        | BASE TABLE             |           1 | INMEMORY_ROWSTORE |
| temptab       | TEMPORARY TABLE        |           1 | INMEMORY_ROWSTORE |
+---------------+------------------------+-------------+-------------------+

Example 3

SHOW TABLES LIKE 's%';
+--------------------+
| Tables_in_db1 (s%) |
+--------------------+
| source             |
| stock              |
| stockN             |
+--------------------+

Example 4

SHOW FULL TABLES WHERE TABLE_TYPE = 'BASE TABLE';
+---------------+------------+
| Tables_in_db1 | Table_type |
+---------------+------------+
| allviews      | BASE TABLE |
| circle        | BASE TABLE |
| product       | BASE TABLE |
| reading       | BASE TABLE |
| source        | BASE TABLE |
| stock         | BASE TABLE |
| stockN        | BASE TABLE |
+---------------+------------+

Last modified: April 26, 2023

Was this article helpful?