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)