Query tables

After a table is successfully created, you can use SQL statements and views to query all tables in the database, as well as the definition and structure of a specific table.

Query tables in a database

After a table is created, you can execute the SHOW TABLES statement to list all tables in a database. The syntax is as follows:

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

Here:

• EXTENDED: displays hidden tables. Since the current version of OceanBase Database does not generate hidden tables, this parameter has no practical effect.

• FULL: displays the table type.

• database_name: the name of the database to be queried.

Here is an example of how to list 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 a table is successfully created, you can execute the SHOW CREATE TABLE statement to view the definition of the table.

Here is an example:

View the definition of the tbl1 table.

SHOW CREATE TABLE tbl1;

The query result is as follows:

+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                             |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl1  | CREATE TABLE `tbl1` (
  `col1` int(11) NOT NULL,
  `col2` int(11) NOT NULL,
  `col3` int(11) DEFAULT NULL,
  PRIMARY KEY (`col1`)
) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

Here:

• ORGANIZATION INDEX: indicates that the table organization mode is INDEX.

• DEFAULT CHARSET = utf8mb4: specifies the default character set as utf8mb4.

• ROW_FORMAT = DYNAMIC: indicates that data is encoded.

• COMPRESSION: the compression method of the table.

• PCTFREE: the percentage of space reserved for updates of the data block.

Query the structure of a table

After a table is successfully created, you can execute the SHOW COLUMNS/FIELDS statement to view the table structure. The syntax is as follows:

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

Here:

• EXTENDED: displays hidden columns.

• FULL: displays the collation, privileges, and comments of the columns.

• table_name: the name of the table to be queried.

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

Here are some examples:

• View the structure 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
  

• View all columns, including hidden ones, 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.

Query table information by using views

You can query information about tables from the following system views:

Here is an example:

SELECT * FROM information_schema.tables WHERE table_name = 'ora_tbl1'\G

The return result is as follows:

*************************** 1. row ***************************
         TABLE_CATALOG: def
          TABLE_SCHEMA: test
            TABLE_NAME: ora_tbl1
            TABLE_TYPE: BASE TABLE
                ENGINE: InnoDB
               VERSION: NULL
            ROW_FORMAT: DYNAMIC
            TABLE_ROWS: 0
        AVG_ROW_LENGTH: 0
           DATA_LENGTH: 0
       MAX_DATA_LENGTH: NULL
          INDEX_LENGTH: 0
             DATA_FREE: NULL
        AUTO_INCREMENT: NULL
           CREATE_TIME: 2025-02-24 15:17:13
           UPDATE_TIME: 2025-02-24 15:17:13
            CHECK_TIME: NULL
       TABLE_COLLATION: utf8mb4_general_ci
              CHECKSUM: NULL
        CREATE_OPTIONS: NULL
         TABLE_COMMENT:
            AUTO_SPLIT: FALSE
AUTO_SPLIT_TABLET_SIZE: 0
          ORGANIZATION: HEAP
1 row in set