SHOW TABLE STATUS

Function

View the table status of the current library (or schema).

Precautions

If db_name is not specified, the query is for the status of the table under the current library (or schema).

Syntax

SHOW TABLE STATUS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

Parameter Description

db_name

Library name (or schema), optional, if not specified, the query is for the current library or schema.
LIKE 'pattern'

pattern matches the first column of the displayed result (column name is 'Name ['pattern']').

Output Field Description

Field	Description
Name	Table name
Engine	Storage engine type. Range of values: USTORE, indicates that the table supports Inplace-Update storage engine. ASTORE, indicates that the table supports Append-Only storage engine.
Version	The default value is NULL
Row_format	Storage method. The range of values: ROW, indicates that the data of the table will be stored in rows; COLUMN, indicates that the data of the table will be stored in columns.
Rows	Number of rows
Avg_row_length	The default value is NULL
Data_length	Data size, obtained from pg_relation_size(oid)
Max_data_length	The default value is NULL
Index_length	Index size, obtained from pg_indexes_size(oid)
Data_free	The default value is NULL
Auto_increment	When the primary key is a sequence, get its last value
Create_time	Creation time
Update_time	Update time
Check_time	The default value is NULL
Collation	ordered set
Checksum	The default value is NULL
Create_options	Options for creating a table
Comment	Comment

Examples

MogDB=# CREATE SCHEMA tst_schema;
MogDB=#
MogDB=# SET SEARCH_PATH TO tst_schema;
MogDB=#
MogDB=# CREATE TABLE tst_t1
MogDB-# (
MogDB(# id serial primary key,
MogDB(# name varchar(20),
MogDB(# phone text
MogDB(# )WITH(ORIENTATION=ROW, STORAGE_TYPE=USTORE);
MogDB=#
MogDB=# COMMENT ON TABLE tst_t1 IS 'this is comment';
MogDB=#
MogDB=# CREATE VIEW tst_v1 AS SELECT * FROM tst_t1;
MogDB=#
MogDB=# CREATE TABLE tst_t2
MogDB-# (
MogDB(# id serial primary key,
MogDB(# name varchar(20),
MogDB(# phone text
MogDB(# )WITH(ORIENTATION=COLUMN);
MogDB=#

-- Viewing Table Status
MogDB=# 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)

-- like Fuzzy Matching
MogDB=# 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)

-- where Conditional Filtering
MogDB=# 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)