SHOW
更新时间：
复制 MD 格式
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 | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+该文章对您有帮助吗？