All Products
Search
Document Center

AnalyticDB:SHOW

Last Updated:Nov 05, 2024

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;            

Examples

SHOW DATABASES;

The following information 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];          

Examples

SHOW TABLES IN adb_demo;

The following information 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;        

Examples

SHOW COLUMNS IN adb_demo.customer;

The following information 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;          

Examples

SHOW CREATE TABLE adb_demo.customer;

The following information is returned:

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

SHOW GRANTS

You can execute the SHOW GRANTS statement to query the permissions of a user.

Syntax

SHOW GRANTS [FOR 'username'@'hostname'];

username specifies the username, and hostname specifies the hostname or the IP address of the host.

Examples

  • View the permissions of the current user without the need to specify the username or hostname.

    SHOW GRANTS;

    The following information is returned:

    +---------------------------------------------------------+
    | Grants for adb_acc@%                                    |
    +---------------------------------------------------------+
    | GRANT ALL ON `*`.`*` TO 'adb_acc'@'%' WITH GRANT OPTION |
    +---------------------------------------------------------+
  • View the permissions of the specified user.

    SHOW GRANTS FOR 'test'@'%';

    The following information is returned:

    +---------------------------------------------------------+
    | Grants for  test@%                                      |
    +---------------------------------------------------------+
    | GRANT ALL ON `*`.`*` TO 'adb'@'%' 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;  

Examples

SHOW INDEXES FROM adb_demo.json_test;

The following information 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      |         |               |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+