You can execute the SHOW statement to view database information such as database lists, database tables, and columns or indexes of tables.

SHOW DATABASES

You can execute the SHOW DATABASES statement to view the databases in the current cluster.

  • Syntax:
    SHOW DATABASES;            
  • Example:
    SHOW DATABASES;
    The following result is returned:
    +--------------------+
    | Database           |
    +--------------------+
    | adb_test           |
    | MYSQL              |
    | adb_demo           |
    | INFORMATION_SCHEMA |
    +--------------------+

SHOW TABLES

You can execute the SHOW TABLES statement to view the tables in the current database.

  • Syntax:
    SHOW TABLES [IN db_name];          
  • Example:
    SHOW TABLES IN adb_demo;
    The following result is returned:
    +--------------------+
    | Tables_in_adb_demo |
    +--------------------+
    | customer           |
    | json_test          |
    +--------------------+

SHOW COLUMNS

You can execute the SHOW COLUMNS statement to view the columns in a table.

  • Syntax:
    SHOW COLUMNS IN db_name.table_name;        
  • Example:
    SHOW COLUMNS IN adb_demo.customer;
    The following result is returned:
    +---------+---------+------+------+---------+-------+
    | Field   | Type    | Null | Key  | Default | Extra |
    +---------+---------+------+------+---------+-------+
    | id      | int     | NO   | PRI  | NULL    |       |
    | name    | varchar | YES  |      | NULL    |       |
    | address | varchar | YES  |      | NULL    |       |
    | gender  | boolean | YES  |      | NULL    |       |
    +---------+---------+------+------+---------+-------+

SHOW CREATE TABLE

You can execute the SHOW CREATE TABLE statement to view the statement that is used to create a table.

  • Syntax:
    SHOW CREATE TABLE db_name.table_name;          
  • Example:
    SHOW CREATE TABLE adb_demo.customer;

    The following result is returned:

    +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table    | Create Table                                                                                                                                                                                                    |
    +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | customer | Create Table `customer` (
     `id` int NOT NULL,
     `name` varchar(50),
     `address` varchar(80),
     `gender` boolean,
     primary key (`id`)
    ) DISTRIBUTE BY HASH(`id`) INDEX_ALL='Y' STORAGE_POLICY='HOT' BLOCK_SIZE=8192 |
    +----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SHOW GRANTS

You can execute the SHOW GRANTS statement to view the permissions that are granted to the current account.

  • Syntax:
    SHOW GRANTS;
  • Example:
    SHOW GRANTS;
    The following result is returned:
    +---------------------------------------------------------+
    | Grants for adb_acc@%                                    |
    +---------------------------------------------------------+
    | GRANT ALL ON `*`.`*` TO 'adb_acc'@'%' WITH GRANT OPTION |
    +---------------------------------------------------------+

SHOW INDEXES

You can execute the SHOW INDEXES statement to view the indexes of a table.

  • Syntax:
    SHOW INDEXES FROM db_name.table_name;  
  • Example:
    SHOW INDEXES FROM adb_demo.json_test;
    The following result is returned. Key_name indicates the name of an index.
    +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | json_test |          1 | id_0_idx |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
    | json_test |          1 | vj_idx   |            1 | vj          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
    +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+