This topic describes the SHOW syntax in Data Definition Language (DDL) statements and provides related examples.

SHOW SCHEMAS

Queries all the schemas in a database of a specific user.

  • Syntax:
    SHOW {SCHEMAS | DATABASES}
    [LIKE 'pattern']
    [EXTRA]
  • Example:
    mysql> show schemas;
+------------------+
| TABLE_SCHEMA     |
+------------------+
| tpch_string_100x |
| tpch_string_10x  |
| tpch_string_1x   |
+------------------+
3 rows in set (0.01 sec)
    mysql> show schemas like 'tpch%';
+------------------+
| TABLE_SCHEMA     |
+------------------+
| tpch_string_100x |
| tpch_string_10x  |
| tpch_string_1x   |
+------------------+
3 rows in set (0.01 sec)
    mysql> show schemas like 'tpch%' extra;
+--------------+------------------+------------------------------+
| CATALOG_NAME | TABLE_SCHEMA     | CREATOR_ID                   |
+--------------+------------------+------------------------------+
| hive         | tpch_string_100x | OA$oa_101302231286633637597c |
| hive         | tpch_string_10x  | OA$oa_101302231286633637597c |
| hive         | tpch_string_1x   | OA$oa_101302231286633637597c |
+--------------+------------------+------------------------------+
3 rows in set (0.01 sec)

SHOW TABLES

Queries all the tables in a schema. You must execute the following statement before you execute the SHOW TABLES statement: 
USE database_name
The preceding statement is used to switch to the required schema.
Example:
mysql> use tpch_string_1x;
Database changed
mysql> show tables;
+-------------------------+
| TABLE_NAME              |
+-------------------------+
| customer_avro_string    |
| customer_json_string    |
| customer_orc_string     |
| customer_parquet_string |
| customer_rcfile_string  |
| customer_text_string    |
+-------------------------+
6 rows in set (0.01 sec)
You can execute the SELECT DATABASE() statement to query the schema of a session. 
mysql> select database();
+----------------+
| database()     |
+----------------+
| tpch_string_1x |
+----------------+
1 row in set (0.00 sec)
mysql> show tables like 'cus%';
+-------------------------+
| TABLE_NAME              |
+-------------------------+
| customer_avro_string    |
| customer_json_string    |
| customer_orc_string     |
| customer_parquet_string |
| customer_rcfile_string  |
| customer_text_string    |
+-------------------------+
6 rows in set (0.01 sec)

SHOW CREATE TABLE

Queries the table creation statement.

  • Syntax:
    SHOW CREATE TABLE tbl_name
  • Example:
    mysql> show create table customer_text_string\G
*************************** 1. row ***************************
Result: CREATE EXTERNAL TABLE `customer_text_string`(
  `c_custkey` int, 
  `c_name` string, 
  `c_address` string, 
  `c_nationkey` int, 
  `c_phone` string, 
  `c_acctbal` double, 
  `c_mktsegment` string, 
  `c_comment` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '|' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
'oss://acs:ram::111111111:role&aliyunopenanalyticsossfullaccessrole@test_bucket/datasets/tpch/1x/text_string/customer_text'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='false', 
  'numFiles'='1', 
  'numRows'='-1', 
  'rawDataSize'='-1', 
  'totalSize'='24346144', 
  'transient_lastDdlTime'='1523448357')

1 row in set (0.38 sec)

SHOW PARTITIONS

Queries information about all partitions of a specific table.

  • Syntax:
    SHOW PARTITIONS
    [database_name.]table_name
  • Example:
    mysql> show partitions primitives_avro_p;
+--------------------------------------------------------------------------+
| Result                                                                   |
+--------------------------------------------------------------------------+
| year=2009/month=1
year=2009/month=2
year=2009/month=3
year=2009/month=4
 |
+--------------------------------------------------------------------------+
1 row in set (0.46 sec)

mysql> show partitions basic_test.primitives_avro_p;
+--------------------------------------------------------------------------+
| Result                                                                   |
+--------------------------------------------------------------------------+
| year=2009/month=1
year=2009/month=2
year=2009/month=3
year=2009/month=4
 |
+--------------------------------------------------------------------------+
1 row in set (0.67 sec)
    You can specify filter conditions to query partitions.
    SHOW PARTITIONS [database_name.]table_name [PARTITION(partition_spec)];
    Example:
    mysql> show partitions primitives_avro_p partition(year='2009');
+--------------------------------------------------------------------------+
| Result                                                                   |
+--------------------------------------------------------------------------+
| year=2009/month=1
year=2009/month=2
year=2009/month=3
year=2009/month=4
 |
+--------------------------------------------------------------------------+
1 row in set (0.51 sec)

mysql> show partitions primitives_avro_p partition(month='3');
+--------------------+
| Result             |
+--------------------+
| year=2009/month=3
 |
+--------------------+
1 row in set (0.45 sec)

mysql> show partitions primitives_avro_p partition(year='2009',month='3');
+--------------------+
| Result             |
+--------------------+
| year=2009/month=3
 |
+--------------------+
1 row in set (0.46 sec)

SHOW QUERY_TASK

Queries the query task information of a specific user.

  • Syntax:
    SHOW QUERY_TASK [FOR UserName]
    [WHERE expr]
    [ORDER BY {col_name | expr}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
  • Example:
    mysql> show query_task where create_time > '2018-04-12' order by create_time desc limit 4, 1\G
*************************** 1. row ***************************
        TABLE_SCHEMA: tpch_string_1x
                  ID: e573ff53_1523547045897
              STATUS: SUCCESS
             MESSAGE: null
             COMMAND: select * from customer_text_string_2
          CREATOR_ID: OA$oa_101302231286633637597c
         CREATE_TIME: 2018-04-12 23:30:46.0
         UPDATE_TIME: 2018-04-12 23:30:46.0
           ROW_COUNT: 10000
         ELAPSE_TIME: 3017
  SCANNED_DATA_BYTES: 1630878
RESULT_FILE_OSS_FILE: oss://aliyun-oa-query-results-*****-oss-cn-hangzhou/*****/2018/4/12/20180412233042e573ff53000017/result.csv
    CANCELLABLE_TASK: 0
1 row in set (0.04 sec)
    mysql> show query_task where id = 'e573ff53_1523547045897'\G
*************************** 1. row ***************************
        TABLE_SCHEMA: tpch_string_1x
                  ID: e573ff53_1523547045897
              STATUS: SUCCESS
             MESSAGE: null
             COMMAND: select * from customer_text_string_2
          CREATOR_ID: OA$oa_101302231286633637597c
         CREATE_TIME: 2018-04-12 23:30:46.0
         UPDATE_TIME: 2018-04-12 23:30:46.0
           ROW_COUNT: 10000
         ELAPSE_TIME: 3017
  SCANNED_DATA_BYTES: 1630878
RESULT_FILE_OSS_FILE:  oss://aliyun-oa-query-results-*****-oss-cn-hangzhou/*****/2018/4/12/20180412233042e573ff53000017/result.csv
    CANCELLABLE_TASK: 0
1 row in set (0.01 sec)
    If you log on to the DLA console with your Alibaba Cloud account, you can query the SQL statement execution history of other users.
mysql> show query_task  for your sun_account where  create_time > '2020-06-01'

SHOW PROCESSLIST

Queries the threads or connections that are running in a server.

  • Syntax:
    show processlist
  • Example:
    mysql> show processlist\G

*************************** 1. row ***************************
       Id: 16777224            -- ID of a thread or connection
     User: oa_1xxxxxxxxxx7597c -- DLA account 
     Host: 212.20.XX.XX:7320  -- IP address and port number of the client
       db: tpch_50x_text       -- Schema bound to the thread or connection
  Command: SELECT              -- Type of the SQL statement
     Time: 1                   -- Time to execute the SQL statement
    State: RUNNING             -- Status of the SQL statement
     Info: select count(*) from lineitem   -- SQL statement
ProcessId: q201908070004sh0aa2bf4a0001009   -- Process ID that is used for troubleshooting
1 row in set (0.08 sec)

SHOW GRANTS

Queries the permissions of an account.

  • Syntax:
    show grants for <account_id>
  • Example:
    GRANT ALL ON *.* TO dla_p123456789 WITH GRANT OPTION
Note ALL indicates all permissions. For more information about permissions, see GRANT.