SHOW TABLE STATUS

功能描述

查看当前库（或schema）的表状态。

注意事项

若不指定db_name，查询的是当前库（或schema）下的表状态。

语法格式

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

参数说明

  • db_name

    1.  库名（或schema），可选项，若不指定，则查询的是当前库或schema。

  • LIKE ‘pattern’

    1.  pattern匹配显示结果第一列（列名为'Name [`pattern`]'）。

输出字段说明

字段含义
Name表名
Engine存储引擎类型。取值范围：USTORE，表示表支持Inplace-Update存储引擎。ASTORE，表示表支持Append-Only存储引擎。
Version默认值NULL
Row_format存储方式。取值范围：ROW，表示表的数据将以行式存储。COLUMN，表示表的数据将以列式存储。
Rows行数
Avg_row_length默认值NULL
Data_length数据大小，由pg_relation_size(oid)获得
Max_data_length默认值NULL
Index_length索引大小，由pg_indexes_size(oid)获得
Data_free默认值NULL
Auto_increment当primary key为sequence时获取其last值
Create_time创建时间
Update_time更新时间
Check_time默认值NULL
Collation排序集
Checksum默认值NULL
Create_options建表选项
Comment注释

示例

  1. opengauss=# CREATE SCHEMA tst_schema;
  2. opengauss=#
  3. opengauss=# SET SEARCH_PATH TO tst_schema;
  4. opengauss=#
  5. opengauss=# CREATE TABLE tst_t1
  6. opengauss-# (
  7. opengauss(# id serial primary key,
  8. opengauss(# name varchar(20),
  9. opengauss(# phone text
  10. opengauss(# )WITH(ORIENTATION=ROW, STORAGE_TYPE=USTORE);
  11. opengauss=#
  12. opengauss=# COMMENT ON TABLE tst_t1 IS 'this is comment';
  13. opengauss=#
  14. opengauss=# CREATE VIEW tst_v1 AS SELECT * FROM tst_t1;
  15. opengauss=#
  16. opengauss=# CREATE TABLE tst_t2
  17. opengauss-# (
  18. opengauss(# id serial primary key,
  19. opengauss(# name varchar(20),
  20. opengauss(# phone text
  21. opengauss(# )WITH(ORIENTATION=COLUMN);
  22. opengauss=#
  24. --查看表状态
  25. opengauss=# show table status;
  26.   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
  27. --------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+------------------------------------------------------+-----------------
  28.  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
  29.  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}                 |
  30.  tst_v1 |        |         |            |    0 |              0 |           0 |               0 |            0 |         0 |                | 2022-10-18 09:04:24 | 2022-10-18 09:04:24 |            | en_US.UTF-8 |          |                                                      |
  31. (3 rows)
  33. --like 模糊匹配
  34. opengauss=# show table status in tst_schema like '%tst_t%';
  35.   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
  36. --------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+------------------------------------------------------+-----------------
  37.  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
  38.  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}                 |
  39. (2 rows)
  41. --where 条件筛选
  42. opengauss=# show table status from tst_schema where Engine='ASTORE';
  43.   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
  44. --------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------+----------+--------------------------------------+---------
  45.  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} |
  46. (1 row)