SHOW TABLE STATUS

功能描述

SHOW TABLES用于查看当前库（或schema）的表状态。

注意事项

该功能仅在数据库兼容模式为MySQL时支持（即数据库实例初始化时指定DBCOMPATIBILITY='B'）。

语法格式

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

参数说明

• db_name

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

• LIKE 'pattern'

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

返回结果集

示例

1、创建模式tst_schema并切换到其路径下。

CREATE SCHEMA tst_schema;
SET SEARCH_PATH TO tst_schema;

2、创建测试表和视图。

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

3、查看当前模式下表的状态。

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 | 2023-08-22 16:03:45 | 2023-08-22 16:03:45 |            | e
n_US.UTF-8 |          | {orientation=ROW,storage_type=ustore,compression=no} | this is comment
 tst_t2 | ASTORE |         | COLUMN     |    0 |              0 |       24576 |               0 |         8192 |         0 |              1 | 2023-08-22 16:03:46 | 2023-08-22 16:03:46 |            | e
n_US.UTF-8 |          | {orientation=column,compression=low}                 |
 tst_v1 |        |         |            |    0 |              0 |           0 |               0 |            0 |         0 |                | 2023-08-22 16:03:45 | 2023-08-22 16:03:45 |            | e
n_US.UTF-8 |          |                                                      |
(3 rows)

4、使用like模糊匹配查看表状态。

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 | 2023-08-22 16:03:45 | 2023-08-22 16:03:45 |            | e
n_US.UTF-8 |          | {orientation=ROW,storage_type=ustore,compression=no} | this is comment
 tst_t2 | ASTORE |         | COLUMN     |    0 |              0 |       24576 |               0 |         8192 |         0 |              1 | 2023-08-22 16:03:46 | 2023-08-22 16:03:46 |            | e
n_US.UTF-8 |          | {orientation=column,compression=low}                 |
(2 rows)

5、在SHOW TABLES STATUS语句中使用WHERE选项。

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 | 2023-08-22 16:03:46 | 2023-08-22 16:03:46 |            | e
n_US.UTF-8 |          | {orientation=column,compression=low} |
(1 row)