文档中心MogDBMogDB StackUqbar
v5.0

文档:v5.0

支持的版本:

其他版本:

SHOW TABLE STATUS

功能描述

查看当前库(或schema)的表状态。

注意事项

若不指定db_name,查询的是当前库(或schema)下的表状态。

语法格式

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

参数说明

  • db_name

    库名(或schema),可选项,若不指定,则查询的是当前库或schema。

  • LIKE 'pattern'

    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 注释

示例

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=#

--查看表状态
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 模糊匹配
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 条件筛选
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)
 
Copyright © 2011-2024 www.enmotech.com All rights reserved.