SHOW COLUMNS

功能描述

查看指定表的列元信息。

注意事项

  • 临时表需要指定临时表对应的schema查询。
  • 复合主键索引所有参与列都会在Key字段中显示为PRI。
  • 复合唯一索引所有参与列都会在Key字段中显示为UNI。
  • 如果一个列参与了多个索引的创建，以该列第一个创建的索引为准显示Key字段。
  • 生成列会在Default中显示生成式。
  • 表名中含schemaname/dbname并且同时指定dbname时，仅匹配指定的dbname。
  • 结果仅显示当前查询用户具有SELECT权限的列信息。

语法格式

  1. SHOW [FULL] {COLUMNS | FIELDS}
  2.    {FROM | IN} tbl_name
  3.    [{FROM | IN} db_name]
  4.    [LIKE 'pattern' | WHERE expr]

参数说明

  • {COLUMNS | FIELDS}

    使用COLUMNS和FIELDS效果是等价的。

  • tbl_name

    表名，可指定表名。也可以指定schema_name.table_name。

  • db_name

    库名（或schema），当tbl_name中也指定库名（或schema名）时，优先选择本选项。

  • LIKE ‘pattern’

    pattern匹配显示结果的Field列。

示例

  1. --创建简单表
  2. openGauss=# CREATE SCHEMA tst_schema1;
  4. openGauss=# SET SEARCH_PATH TO tst_schema1;
  6. openGauss=# CREATE TABLE tst_t1
  7. openGauss-# (
  8. openGauss(# id int primary key,
  9. openGauss(# name varchar(20) NOT NULL,
  10. openGauss(# addr text COLLATE "de_DE",
  11. openGauss(# phone text COLLATE "es_ES",
  12. openGauss(# addr_code text
  13. openGauss(# );
  14. openGauss=# COMMENT ON COLUMN tst_t1.id IS 'identity';
  16. --查看表的列元信息
  17. openGauss=# SHOW COLUMNS FROM tst_t1;
  18.    Field   |         Type          | Null | Key | Default | Extra 
  19. -----------+-----------------------+------+-----+---------+-------
  20.  id        | integer               | NO   | PRI | NULL    | 
  21.  name      | character varying(20) | NO   |     | NULL    | 
  22.  addr      | text                  | YES  |     | NULL    | 
  23.  phone     | text                  | YES  |     | NULL    | 
  24.  addr_code | text                  | YES  |     | NULL    | 
  26. openGauss=# show FULL COLUMNS FROM tst_t1;
  27.    Field   |         Type          | Collation | Null | Key | Default | Extra |               Privileges                | Comment  
  28. -----------+-----------------------+-----------+------+-----+---------+-------+-----------------------------------------+----------
  29.  id        | integer               | NULL      | NO   | PRI | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | identity
  30.  name      | character varying(20) | NULL      | NO   |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
  31.  addr      | text                  | de_DE     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
  32.  phone     | text                  | es_ES     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
  33.  addr_code | text                  | NULL      | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
  35.  openGauss=# show FULL COLUMNS FROM tst_schema1.tst_t1;
  36.    Field   |         Type          | Collation | Null | Key | Default | Extra |               Privileges                | Comment  
  37. -----------+-----------------------+-----------+------+-----+---------+-------+-----------------------------------------+----------
  38.  id        | integer               | NULL      | NO   | PRI | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | identity
  39.  name      | character varying(20) | NULL      | NO   |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
  40.  addr      | text                  | de_DE     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
  41.  phone     | text                  | es_ES     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
  42.  addr_code | text                  | NULL      | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
  44. --模糊匹配、过滤
  45. openGauss=# show full columns from tst_t1 like '%addr%';
  46.    Field   | Type | Collation | Null | Key | Default | Extra |               Privileges                | Comment 
  47. -----------+------+-----------+------+-----+---------+-------+-----------------------------------------+---------
  48.  addr      | text | de_DE     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
  49.  addr_code | text | NULL      | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
  51. openGauss=# show full columns from tst_t1 where Type='text';
  52.    Field   | Type | Collation | Null | Key | Default | Extra |               Privileges                | Comment 
  53. -----------+------+-----------+------+-----+---------+-------+-----------------------------------------+---------
  54.  addr      | text | de_DE     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
  55.  phone     | text | es_ES     | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
  56.  addr_code | text | NULL      | YES  |     | NULL    |       | UPDATE,SELECT,REFERENCES,INSERT,COMMENT | 
  58. --显示权限过滤
  59. openGauss=# CREATE USER tst_u1 PASSWORD 'tst_u1@123';
  60. openGauss=# SET ROLE tst_u1 PASSWORD 'tst_u1@123';
  61. opengauss=> SET SEARCH_PATH TO tst_schema1;
  63. openGauss=> show full columns from tst_t1;
  64.  Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment 
  65. -------+------+-----------+------+-----+---------+-------+------------+---------
  66. (0 rows)
  68. openGauss=# RESET ROLE;
  69. opengauss=# GRANT SELECT (addr, phone) on tst_t1 to tst_u1;
  70. openGauss=# SET ROLE tst_u1 PASSWORD 'tst_u1@123';
  72. opengauss=> SET SEARCH_PATH TO tst_schema1;
  73. openGauss=> show full columns from tst_t1;
  74.   Field   | Type | Collation | Null | Key | Default | Extra | Privileges  | Comment 
  75. -----------+------+-----------+------+-----+---------+-------+------------------+---------
  76.  addr      | text | de_DE     | YES  |     | NULL    |       | SELECT     | 
  77.  phone     | text | es_ES     | YES  |     | NULL    |       | SELECT     |