SHOW TABLES

功能描述

查看当前库（或schema)的表清单。

注意事项

• 若不指定db_name，查询的是当前库(或schema)下的表清单。

语法格式

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

参数说明

• db_name

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

pattern匹配显示结果第一列(列名为’Tables_in_dbname [pattern]‘）。

示例

1. --创建简单表
2. openGauss=# CREATE SCHEMA tst_schema;
3. openGauss=# SET SEARCH_PATH TO tst_schema;

5. openGauss=# CREATE TABLE tst_t1
6. openGauss-# (
7. openGauss(# id int primary key,
8. openGauss(# name varchar(20) NOT NULL,
9. openGauss(# addr text COLLATE "de_DE",
10. openGauss(# phone text COLLATE "es_ES",
11. openGauss(# addr_code text
12. openGauss(# );
13. openGauss=# CREATE VIEW tst_v1 AS SELECT * FROM tst_t1;
14. openGauss=# CREATE TABLE t_t2(id int);

16. --查看库（或SCHEMA）下表清单信息
17. openGauss=# show tables;
18. Tables_in_tst_schema
19. ----------------------
20. tst_t1
21. tst_v1
22. t_t2

24. openGauss=# show full tables;
25. Tables_in_tst_schema | Table_type
26. ----------------------+------------
27. tst_t1               | BASE TABLE
28. tst_v1               | VIEW
29. t_t2                 | BASE TABLE

31. openGauss=# show full tables in tst_schema;
32. Tables_in_tst_schema | Table_type
33. ----------------------+------------
34. tst_t1               | BASE TABLE
35. tst_v1               | VIEW
36. t_t2                 | BASE TABLE

38. --模糊匹配、过滤
39. openGauss=# show full tables like '%tst%';
40. Tables_in_tst_schema (%tst%) | Table_type
41. ------------------------------+------------
42. tst_t1                       | BASE TABLE
43. tst_v1                       | VIEW

45. openGauss=# show full tables where Table_type='VIEW';
46. Tables_in_tst_schema | Table_type
47. ----------------------+------------
48. tst_v1               | VIEW