Query tables

After you create a table, you can query its definition and schema or query all tables in the database by using SQL statements.

Query all tables in a database

You can use the SHOW TABLES statement to query all tables in a database. The syntax is as follows:

SHOW [EXTENDED] [FULL] TABLES [{FROM | IN} database_name];

The parameters are described as follows:

• EXTENDED: specifies to show hidden tables. This keyword does not take effect because the current version of OceanBase Database does not generate hidden tables.

• FULL: specifies to show the table type.

• database_name: the name of the database whose tables are to be queried.

The following example queries all tables in the test database.

SHOW TABLES FROM test;

The query result is as follows:

+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
1 row in set

For more information about the SHOW TABLES statement, see SHOW.

Query the definition of a table

After you create a table, you can use the SHOW CREATE TABLE statement to query the definition of the table.

The following example queries the definition of the test table.

obclient [test]> SHOW CREATE TABLE test;

The return result is as follows:

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `name` char(50) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Some of the fields in the result are described as follows:

• DEFAULT CHARSET = utf8mb4: indicates that the default character set is utf8mb4.

• ROW_FORMAT = DYNAMIC: indicates that data encoding is enabled.

• COMPRESSION: the compression method of the table.

• PCTFREE: the space reserved for updating this data block.

Query the schema of a table

After you create a table, you can use the SHOW COLUMNS or SHOW FIELDS statement to query the schema of the table. The syntax is as follows:

SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS}
    {FROM | IN} table_name
    [{FROM | IN} database_name];

The parameters are described as follows:

• EXTENDED: specifies to show hidden columns.

• FULL: specifies to show the collation, privileges, and comments of columns.

• table_name: the name of the table whose schema is to be queried.

• database_name: the name of the database to which the table belongs.

Here are some examples:

• Query the schema of the test table.

  obclient [test]> SHOW COLUMNS FROM test;
  

  The query result is as follows:

  +-------+----------+------+-----+---------+-------+
  | Field | Type     | Null | Key | Default | Extra |
  +-------+----------+------+-----+---------+-------+
  | id    | int(11)  | YES  |     | NULL    |       |
  | name  | char(50) | YES  |     | NULL    |       |
  +-------+----------+------+-----+---------+-------+
  2 rows in set
  

• Query all columns, including hidden columns, in the test table.

  obclient [test]> SHOW EXTENDED COLUMNS FROM test;
  

  The query result is as follows:

  +----------------+-----------------+------+-----+---------+-------+
  | Field          | Type            | Null | Key | Default | Extra |
  +----------------+-----------------+------+-----+---------+-------+
  | id             | int(11)         | YES  |     | NULL    |       |
  | name           | char(50)        | YES  |     | NULL    |       |
  | __pk_increment | bigint unsigned | NO   | PRI | NULL    |       |
  +----------------+-----------------+------+-----+---------+-------+
  3 rows in set
  

For more information about the SHOW COLUMNS or SHOW FIELDS statement, see SHOW.

Check a table

You can use the CHECK TABLE statement to check whether a table exists in the database.

For more information about the CHECK TABLE statement, see CHECK TABLE.

Here is an example:

1. Assume that the tbl1 table does not exist. Execute the following statement and view the return result:

   obclient [test]> CHECK TABLE tbl1;
   

   The return result is as follows:

   +-----------+-------+----------+---------------------------------+
   | Table     | Op    | Msg_type | Msg_text                        |
   +-----------+-------+----------+---------------------------------+
   | test.tbl1 | check | Error    | Table 'test.tbl1' doesn't exist |
   | test.tbl1 | check | status   | Operation failed                |
   +-----------+-------+----------+---------------------------------+
   2 rows in set
   

2. Create a table named tbl1.

   obclient [test]> CREATE TABLE tbl1(col1 INT, col2 VARCHAR(18));
   

3. Execute the following statement and view the return result:

   obclient [test]> CHECK TABLE tbl1;
   

   The return result is as follows:

   +-----------+-------+----------+----------+
   | Table     | Op    | Msg_type | Msg_text |
   +-----------+-------+----------+----------+
   | test.tbl1 | check | status   | OK       |
   +-----------+-------+----------+----------+
   1 row in set