SHOW

The SHOW keyword provides database and table information.

SHOW DATABASES

Show all databases:

  1. SHOW DATABASES;
  1. +---------+
  2. | Schemas |
  3. +---------+
  4. | public  |
  5. +---------+
  6. 1 row in set (0.01 sec)

Show databases by LIKE pattern:

  1. SHOW DATABASES LIKE 'p%';

Show databases by where expr:

  1. SHOW DATABASES WHERE Schemas='test_public_schema';

SHOW TABLES

Show all tables:

  1. SHOW TABLES;
  1. +---------+
  2. | Tables  |
  3. +---------+
  4. | numbers |
  5. | scripts |
  6. +---------+
  7. 2 rows in set (0.00 sec)

Show tables in the test database:

  1. SHOW TABLES FROM test;

Show tables by like pattern:

  1. SHOW TABLES like '%prometheus%';

Show tables by where expr:

  1. SHOW TABLES FROM test WHERE Tables='numbers';

SHOW FULL TABLES

  1. SHOW FULL TABLES [IN | FROM] [DATABASE] [LIKE pattern] [WHERE query]

It will list all tables and table types in the database:

  1. SHOW FULL TABLES;
  1. +---------+------------+
  2. | Tables  | Table_type |
  3. +---------+------------+
  4. | monitor | BASE TABLE |
  5. | numbers | TEMPORARY  |
  6. +---------+------------+
  7. 2 rows in set (0.00 sec)
  • Tables: the table names.
  • Table_type: the table types, such as BASE_TABLE, TEMPORARY, and VIEW etc.

It supports like and where query too:

  1. SHOW FULL TABLES FROM public like '%mo%';
  1. +---------+------------+
  2. | Tables  | Table_type |
  3. +---------+------------+
  4. | monitor | BASE TABLE |
  5. +---------+------------+
  6. 1 row in set (0.01 sec)
  1. SHOW FULL TABLES WHERE Table_type='BASE TABLE';
  1. +---------+------------+
  2. | Tables  | Table_type |
  3. +---------+------------+
  4. | monitor | BASE TABLE |
  5. +---------+------------+
  6. 1 row in set (0.01 sec)

SHOW CREATE TABLE

Shows the CREATE TABLE statement that creates the named table:

  1. SHOW CREATE TABLE [table]

For example:

  1. SHOW CREATE TABLE system_metrics;
  1. +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  2. | Table          | Create Table                                                                                                                                                                                                                                                                                                                 |
  3. +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  4. | system_metrics | CREATE TABLE IF NOT EXISTS `system_metrics` (
  5.   `host` STRING NULL,
  6.   `idc` STRING NULL,
  7.   `cpu_util` DOUBLE NULL,
  8.   `memory_util` DOUBLE NULL,
  9.   `disk_util` DOUBLE NULL,
  10.   `ts` TIMESTAMP(3) NOT NULL DEFAULT current_timestamp(),
  11.   TIME INDEX (`ts`),
  12.   PRIMARY KEY (`host`, `idc`)
  13. )
  15. ENGINE=mito
  16. WITH(
  17.   regions = 1
  18. ) |
  19. +----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  • Table: the table name.
  • Create Table: The SQL to create the table.