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