SHOW TABLE STATUS
Function
Views the table status of the current database (or schema).
Precautions
If db_name is not specified, the status of tables in the current database (or schema) is queried.
Syntax
SHOW TABLE STATUS
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
Parameter Description
-
db_name
Specifies the database name (or schema). This parameter is optional. If it is not specified, the current database or schema is queried. -
LIKE 'pattern'
Indicates that the pattern matches the first column (column name: 'Name [`pattern`]') in the displayed result.
Output Column Description
| Column | Description |
|---|---|
| Name | Specifies a table name. |
| Engine | Specifies a storage engine type. Value range: USTORE, indicating that the table supports the Inplace-Update storage engine. ASTORE indicates that tables support the append-only storage engine. |
| Version | Default value: NULL. |
| Row_format | Specifies a storage mode. Value range: ROW, indicating that table data will be stored in rows. COLUMN indicates that the data is stored in columns. |
| Rows | Specifies the number of rows. |
| Avg_row_length | Default value: NULL. |
| Data_length | Specifies a data size, which is obtained from pg_relation_size(oid). |
| Max_data_length | Default value: NULL. |
| Index_length | Specifies an index size, which is obtained from pg_indexes_size(oid). |
| Data_free | Default value: NULL. |
| Auto_increment | Obtains the last value when the primary key is a sequence. |
| Create_time | Specifies the creation time. |
| Update_time | Specifies the update time. |
| Check_time | Default value: NULL. |
| Collation | Specifies a collocation set. |
| Checksum | Default value: NULL. |
| Create_options | Specifies table creation options. |
| Comment | Specifies comments. |
Examples
opengauss=# CREATE SCHEMA tst_schema;
opengauss=#
opengauss=# SET SEARCH_PATH TO tst_schema;
opengauss=#
opengauss=# CREATE TABLE tst_t1
opengauss-# (
opengauss(# id serial primary key,
opengauss(# name varchar(20),
opengauss(# phone text
opengauss(# )WITH(ORIENTATION=ROW, STORAGE_TYPE=USTORE);
opengauss=#
opengauss=# COMMENT ON TABLE tst_t1 IS 'this is comment';
opengauss=#
opengauss=# CREATE VIEW tst_v1 AS SELECT * FROM tst_t1;
opengauss=#
opengauss=# CREATE TABLE tst_t2
opengauss-# (
opengauss(# id serial primary key,
opengauss(# name varchar(20),
opengauss(# phone text
opengauss(# )WITH(ORIENTATION=COLUMN);
opengauss=#
--Check the table status.
opengauss=# show table status;
Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment
--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+------------------------------------------------------+-----------------
tst_t1 | USTORE | | ROW | 0 | 0 | 0 | 0 | 57344 | 0 | 1 | 2022-10-18 09:04:24 | 2022-10-18 09:04:24 | | en_US.UTF-8 | | {orientation=row,storage_type=ustore,compression=no} | this is comment
tst_t2 | ASTORE | | COLUMN | 0 | 0 | 24576 | 0 | 8192 | 0 | 1 | 2022-10-18 09:04:24 | 2022-10-18 09:04:24 | | en_US.UTF-8 | | {orientation=column,compression=low} |
tst_v1 | | | | 0 | 0 | 0 | 0 | 0 | 0 | | 2022-10-18 09:04:24 | 2022-10-18 09:04:24 | | en_US.UTF-8 | | |
(3 rows)
--Use the like fuzzy match.
opengauss=# show table status in tst_schema like '%tst_t%';
Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment
--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+------------------------------------------------------+-----------------
tst_t1 | USTORE | | ROW | 0 | 0 | 0 | 0 | 57344 | 0 | 1 | 2022-10-18 09:04:24 | 2022-10-18 09:04:24 | | en_US.UTF-8 | | {orientation=row,storage_type=ustore,compression=no} | this is comment
tst_t2 | ASTORE | | COLUMN | 0 | 0 | 24576 | 0 | 8192 | 0 | 1 | 2022-10-18 09:04:24 | 2022-10-18 09:04:24 | | en_US.UTF-8 | | {orientation=column,compression=low} |
(2 rows)
--Filter by the where condition.
opengauss=# show table status from tst_schema where Engine='ASTORE';
Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment
--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+--------------------------------------+---------
tst_t2 | ASTORE | | COLUMN | 0 | 0 | 24576 | 0 | 8192 | 0 | 1 | 2022-10-18 09:04:24 | 2022-10-18 09:04:24 | | en_US.UTF-8 | | {orientation=column,compression=low} |
(1 row)