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
};
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 ODP. |
| TABLEGROUPS | Queries table groups. |
| {GLOBAL SESSION LOCAL} STATUS | Queries 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. |
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 +---------+-----------------------+--------------------+--------+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 +--------------------+---------+-----+---------+----------+---------+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 +---------------------------------+---------------+-------+----------------------------+