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];

where

• EXTENDED specifies to show hidden tables. The current version of OceanBase Database does not generate hidden tables. Therefore, this parameter has no actual impact in the current version.

• FULL specifies to show the table type.

• database_name specifies 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 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

where

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

• ROW_FORMAT = DYNAMIC indicates that the table is a dynamic table. If a table contains fields of the VARCHAR type, TEXT type or its variants, and BLOB type or its variants, the table is a dynamic table. In other words, ROW_FORMAT of the table is DYNAMIC. If ROW_FORMAT of a table is FIXED, the table is a static table.

• COMPRESSION indicates the compression method of the table.

• PCTFREE indicates 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/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];

where

• EXTENDED specifies to show hidden columns.

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

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

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

Here are some examples:

• The following example queries 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  | varchar(50) | YES  |     | NULL    |       |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set
  

• The following example queries 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/FIELDS statement, see SHOW.