Use the SHOW statement to inspect database objects such as databases, tables, columns, indexes, and user grants.
SHOW DATABASES
List all databases in the current cluster. You can optionally specify a catalog to list the databases that belong to that catalog.
Syntax
SHOW DATABASES [FROM <catalog_name>];Examples
List all databases in the current catalog (the default catalog is adb):
SHOW DATABASES;Output:
+--------------------+
| Database |
+--------------------+
| adb_test |
| MYSQL |
| adb_demo |
| INFORMATION_SCHEMA |
+--------------------+List all databases in an external catalog named paimon_catalog:
SHOW DATABASES FROM paimon_catalog;Output:
+--------------------+
| Database |
+--------------------+
| paimon_db1 |
| paimon_db2 |
| paimon_demo |
+--------------------+SHOW TABLES
List all tables in the current database or a specified database.
Syntax
SHOW TABLES [IN db_name];Example
SHOW TABLES IN adb_demo;Output:
+--------------------+
| Tables_in_adb_demo |
+--------------------+
| customer |
| json_test |
+--------------------+SHOW COLUMNS
List all columns in a table, including data type, nullability, key constraints, and default values.
Syntax
SHOW COLUMNS IN db_name.table_name;Output columns
| Column | Description |
|---|---|
Field |
Column name. |
Type |
Data type of the column, such as int, varchar, or boolean. |
Null |
Whether the column accepts NULL values. YES means it does; NO means it does not. |
Key |
Key constraint. PRI indicates a primary key column. |
Default |
Default value for the column. NULL means no default is set. |
Extra |
Additional attributes. |
Example
SHOW COLUMNS IN adb_demo.customer;Output:
+---------+---------+------+------+---------+-------+
| 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
Retrieve the DDL statement used to create a table.
Syntax
SHOW CREATE TABLE db_name.table_name;Example
SHOW CREATE TABLE adb_demo.customer;Output:
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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
Query the permissions granted to a user.
Syntax
SHOW GRANTS [FOR 'username'@'hostname'];| Parameter | Description |
|---|---|
username |
The username to query. |
hostname |
The hostname or IP address of the host. |
Omit FOR 'username'@'hostname' to view the permissions of the current user.
Examples
View permissions of the current user:
SHOW GRANTS;Output:
+---------------------------------------------------------+
| Grants for adb_acc@% |
+---------------------------------------------------------+
| GRANT ALL ON `*`.`*` TO 'adb_acc'@'%' WITH GRANT OPTION |
+---------------------------------------------------------+View permissions of a specified user:
SHOW GRANTS FOR 'test'@'%';Output:
+---------------------------------------------------------+
| Grants for test@% |
+---------------------------------------------------------+
| GRANT ALL ON `*`.`*` TO 'adb'@'%' WITH GRANT OPTION |
+---------------------------------------------------------+SHOW INDEXES
List all indexes on a table.
Syntax
SHOW INDEXES FROM db_name.table_name;Example
SHOW INDEXES FROM adb_demo.json_test;Output. 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 | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+