SHOW|V4.1.0| docs|Distributed Database

SHOW

Last Updated：2023-07-28 02:55:42 Updated

Purpose

You can use this statement to query information about database objects.

Syntax

SHOW {
    [FULL] TABLES [{FROM | IN} database_name] [like_or_where_clause]
   | {DATABASES | SCHEMAS} [STATUS] [like_or_where_clause]
   | [FULL] {COLUMNS | FIELDS} {FROM | IN} rel_name [{FROM | IN} database_name] [like_or_where_clause]
   | TABLE STATUS [{FROM | IN} database_name] [like_or_where_clause]
   | PROCEDURE STATUS [{FROM | IN} database_name] [like_or_where_clause]
   | FUNCTION STATUS [{FROM | IN} database_name] [like_or_where_clause]
   | {GLOBAL | SESSION | LOCAL} VARIABLES [like_or_where_clause]
   | {CHARSET | CHARACTER SET} [like_or_where_clause]
   | TRACE [like_or_where_clause]
   | COLLATION [like_or_where_clause]
   | PARAMETERS [like_or_where_clause] tenant_name
   | {INDEX | INDEXES | KEYS} {FROM | IN} table_name [{FROM | IN} database_name] WHERE opt_hint_value expr
   | [FULL] PROCESS LIST
   | TABLEGROUPS [like_or_where_clause]
   | {GLOBAL | SESSION | LOCAL} STATUS [like_or_where_clause]
   | TENANT [STATUS]
   | CREATE TENANT tenant_name
   | CREATE TABLEGROUP tablegroup_name
   | CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name
   | CREATE TABLE table_name
   | CREATE VIEW view_name
   | CREATE PROCEDURE proc_name
   | CREATE FUNCTION func_name
   | WARNINGS [LIMIT N | LIMIT N,M]
   | ERRORS [LIMIT N | LIMIT N,M]
   | COUNT(*) WARNINGS
   | COUNT(*) ERRORS
   | GRANTS opt_for_grant_user
   | [STORAGE] ENGINES
   | PRIVILEGES
   | RECYCLEBIN
   | SEQUENCE [like_or_where_clause]
};

like_or_where_clause:
    LIKE like_str [ESCAPE] esc_str
  | WHERE expr

opt_for_grant_user:
    FOR CURRENT USER
  | FOR user_name

Parameters

Parameter	Description
[FULL] TABLES {FROM \| IN} database_name	Queries all tables in `database_name`. The `FULL` keyword specifies the table type.
{DATABASES \| SCHEMAS} [STATUS]	Queries all databases in the current tenant. The `STATUS` keyword specifies the read/write attribute of the database.
[FULL] {COLUMNS \| FIELDS} {FROM \| IN} rel_name	Queries columns of the `rel_name` relationship. The `FULL` keyword specifies the collation, privileges, and comments of the column.
TABLE STATUS [{FROM \| IN} database_name]	Queries details of all tables in `database_name`.
PROCEDURE STATUS [{FROM \| IN} database_name]	Queries details of all stored procedures in `database_name`.
FUNCTION STATUS [{FROM \| IN} database_name]	Queries details of all functions in `database_name`.
{GLOBAL \| SESSION \| LOCAL} VARIABLES	Queries system variables.
{CHARSET \| CHARACTER SET}	Queries supported character sets.
COLLATION	Queries supported collations.
PARAMETERS [like_or_where_clause] tenant_name	Queries system parameters.
{INDEX \| INDEXES \| KEYS} {FROM \| IN} rel_name [{FROM \| IN} database_name]	Queries indexes or keys of the `rel_name` relationship.
[FULL] PROCESS LIST	Queries the current session and its status. The `FULL` keyword specifies the IP address and port number of the connection and the session ID of the OceanBase Database Proxy (ODP).
TABLEGROUPS	Queries table groups.
{GLOBAL \| SESSION \| LOCAL} STATUS	Queries about the status of the session.
TENANT [STATUS]	Queries the name of the current tenant. The `STATUS` keyword specifies the read/write status of the tenant.
CREATE TENANT tenant_name	Queries the CREATE TENANT statement. The sys tenant can query the CREATE statements of all tenants. A user tenant can query only its own CREATE statement.
CREATE TABLEGROUP tablegroup_name	Queries the CREATE TABLEGROUP statement.
CREATE {DATABASE \| SCHEMA} [IF NOT EXISTS] database_name	Queries the CREATE DATABASE statement. The `IF NOT EXISTS` keyword is used to add `IF NOT EXISTS` to the create statement.
CREATE TABLE table_name	Queries the CREATE TABLE statement.
CREATE VIEW view_name	Queries the CREATE VIEW statement.
CREATE PROCEDURE proc_name	Queries the CREATE PROCEDURE statement.
CREATE FUNCTION func_name	Queries the CREATE FUNCTION statement.
WARNINGS [LIMIT N \| LIMIT N,M]	Queries alerts.
COUNT(*) WARNINGS	Queries alerts.
ERRORS [LIMIT N \| LIMIT N,M]	Queries errors.
COUNT(*) ERRORS	Queries errors.
GRANTS opt_for_grant_user	Queries privileges of the specified user. If you do not specify the username, privileges of the current user are displayed. The current user can query its own privileges. To query privileges of other users, the current user must have the `SELECT` privilege on the `mysql.user` table.
[STORAGE] ENGINES	Queries storage engines. The storage engine of OceanBase Database is `OceanBase`.
PRIVILEGES	Queries the description of MySQL privileges.
RECYCLEBIN	Queries the recycle bin.
SEQUENCE	Queries the created sequence.

Examples

Query all databases in the current tenant.

obclient> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| oceanbase          |
| information_schema |
| mysql              |
| SYS                |
| LBACSYS            |
| ORAAUDITOR         |
| test               |
| db1                |
+--------------------+

Query supported character sets.

obclient> SHOW CHARSET;
+---------+-----------------------+--------------------+--------+
| Charset | Description           | Default collation  | Maxlen |
+---------+-----------------------+--------------------+--------+
| binary  | Binary pseudo charset | binary             |      1 |
| utf8mb4 | UTF-8 Unicode         | utf8mb4_general_ci |      4 |
| gbk     | GBK charset           | gbk_chinese_ci     |      2 |
| utf16   | UTF-16 Unicode        | utf16_general_ci   |      2 |
| gb18030 | GB18030 charset       | gb18030_chinese_ci |      4 |
| latin1  | cp1252 West European  | latin1_swedish_ci  |      1 |
+---------+-----------------------+--------------------+--------+
6 rows in set

Query supported collations.

obclient> SHOW COLLATION;
+--------------------+---------+-----+---------+----------+---------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen |
+--------------------+---------+-----+---------+----------+---------+
| utf8mb4_general_ci | utf8mb4 |  45 | Yes     | Yes      |       1 |
| utf8mb4_bin        | utf8mb4 |  46 |         | Yes      |       1 |
| binary             | binary  |  63 | Yes     | Yes      |       1 |
| gbk_chinese_ci     | gbk     |  28 | Yes     | Yes      |       1 |
| gbk_bin            | gbk     |  87 |         | Yes      |       1 |
| utf16_general_ci   | utf16   |  54 | Yes     | Yes      |       1 |
| utf16_bin          | utf16   |  55 |         | Yes      |       1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 |         | Yes      |       1 |
| utf16_unicode_ci   | utf16   | 101 |         | Yes      |       1 |
| gb18030_chinese_ci | gb18030 | 248 | Yes     | Yes      |       1 |
| gb18030_bin        | gb18030 | 249 |         | Yes      |       1 |
| latin1_swedish_ci  | latin1  |   8 | Yes     | Yes      |       1 |
| latin1_bin         | latin1  |  47 |         | Yes      |       1 |
+--------------------+---------+-----+---------+----------+---------+
13 rows in set

Query the status of the current session.

obclient> SHOW SESSION STATUS;
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Threads_connected | 16     |
| Uptime            | 945571 |
+-------------------+--------+

Query the definitions of privileges.

obclient> SHOW PRIVILEGES;
+----------------+---------------------------------------+-------------------------------------------------------+
| Privilege      | Context                               | Comment                                               |
+----------------+---------------------------------------+-------------------------------------------------------+
| Alter          | Tables                                | To alter the table                                    |
| Create         | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create user    | Server Admin                          | To create new users                                   |
| Create view    | Tables                                | To create new views                                   |
| Delete         | Tables                                | To delete existing rows                               |
| Drop           | Databases,Tables                      | To drop databases, tables, and views                  |
| Grant option   | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index          | Tables                                | To create or drop indexes                             |
| Insert         | Tables                                | To insert data into tables                            |
| Process        | Server Admin                          | To view the plain text of currently executing queries |
| Select         | Tables                                | To retrieve rows from table                           |
| Show databases | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view      | Tables                                | To see views with SHOW CREATE VIEW                    |
| Super          | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Update         | Tables                                | To update existing rows                               |
| Usage          | Server Admin                          | No privileges - allow connect only                    |
+----------------+---------------------------------------+-------------------------------------------------------+

Query the recycle bin.

obclient> SHOW RECYCLEBIN;
+---------------------------------+---------------+-------+----------------------------+
| OBJECT_NAME                     | ORIGINAL_NAME | TYPE  | CREATETIME                 |
+---------------------------------+---------------+-------+----------------------------+
| __recycle_$_10_1629093637659648 | t1            | TABLE | 2021-08-16 14:00:37.660550 |
| __recycle_$_10_1629093721748992 | t1            | TABLE | 2021-08-16 14:02:01.748691 |
+---------------------------------+---------------+-------+----------------------------+

Query the created sequence.

obclient> CREATE SEQUENCE IF NOT EXISTS s3 ORDER CACHE 100 INCREMENT BY 3 MAXVALUE 99999999999;
Query OK, 0 rows affected

obclient> SHOW SEQUENCE LIKE 's%';
+------------------------+
| Sequences_in_test (s%) |
+------------------------+
| s1                     |
| s2                     |
| s3                     |
+------------------------+
3 rows in set

obclient> SHOW SEQUENCE WHERE sequence_name='s3';
+-------------------+
| Sequences_in_test |
+-------------------+
| s3                |
+-------------------+
1 row in set

obclient> SHOW SEQUENCE;
+-------------------+
| Sequences_in_test |
+-------------------+
| a2                |
| s1                |
| s2                |
| s3                |
+-------------------+
4 rows in set