SHOW COLUMNS

Lists the columns in the tables or views and the dimensions, facts, and metrics in the semantic views for which you have access privileges. This command can be used to list the columns, dimensions, facts, and metrics for the following objects:

  • The specified table or view.

  • All tables and views in the specified schema or in the schema that is currently in use.

  • All tables and views in the specified database or in the database that is currently in use.

  • All tables and views in your account.

另请参阅：

DESCRIBE TABLE

COLUMNS 视图 (Information Schema)

语法

SHOW COLUMNS [ LIKE '<pattern>' ]
             [ IN { ACCOUNT | DATABASE [ <database_name> ] | SCHEMA [ <schema_name> ] | TABLE | [ TABLE ] <table_name> | VIEW | [ VIEW ] <view_name> } | APPLICATION <application_name> | APPLICATION PACKAGE <application_package_name> ]
Copy

参数

LIKE '<pattern>'

按对象名称筛选命令输出。筛选器使用 不区分大小写 的模式匹配，并支持 SQL 通配符（% 和 _）。

例如，以下模式返回相同的结果：

... LIKE '%testing%' ...
... LIKE '%TESTING%' ...
IN { ACCOUNT | DATABASE [ <database_name> ] | SCHEMA [ <schema_name> ] | TABLE | [ TABLE ] <table_name> | VIEW | [ VIEW ] <view_name> | APPLICATION <application_name>  | APPLICATION PACKAGE <application_package_name> }

指定命令的作用域，作用域决定命令是仅列出当前/指定数据库、架构、表或视图的记录，还是列出整个账户中的记录：

如果您指定关键字 ACCOUNT，则该命令将检索当前账户的所有数据库中所有架构的记录。

如果您指定关键字 DATABASE，那么：

  • 如果指定 db_name，则该命令将检索指定数据库的所有架构的记录。

  • If you don't specify a db_name, then:

    • 如果存在当前数据库，则该命令将检索当前数据库中所有架构的记录。

    • 如果当前没有数据库，则该命令将检索账户中所有数据库和架构的记录。

如果您指定关键字 SCHEMA，那么：

  • If you specify a qualified schema name (for example, my_database.my_schema), then the command retrieves records for the specified database and schema.

  • 如果您指定了未限定的 schema_name，那么：

    • 如果存在当前数据库，则该命令将检索当前数据库中指定架构的记录。

    • 如果当前没有数据库，则该命令会显示错误 SQL compilation error: Object does not exist, or operation cannot be performed。

  • If you don't specify a schema_name, then:

    • 如果当前有数据库，那么：

      • 如果当前存在架构，则该命令将检索当前数据库中当前架构的记录。

      • 如果不存在当前架构，则该命令将检索当前数据库中所有架构的记录。

    • 如果当前没有数据库，则该命令将检索账户中所有数据库和所有架构的记录。

如果指定了不带 table_name 的 TABLE 关键字，则：

  • 如果当前有数据库，那么：

    • 如果当前存在架构，则该命令将检索当前数据库中当前架构的记录。

    • 如果不存在当前架构，则该命令将检索当前数据库中所有架构的记录。

  • 如果当前没有数据库，则该命令将检索账户中所有数据库和所有架构的记录。

如果指定了 :code:` <table_name> ` （带或不带 TABLE 关键字），则：

  • 如果指定了完全限定的 :code:` <table_name> ` （例如 my_database_name.my_schema_name.my_table_name），则该命令将检索指定表的所有记录。

  • 如果指定了架构限定的 :code:` <table_name> ` （例如 my_schema_name.my_table_name），则：

    • 如果当前数据库存在，该命令将检索指定表的所有记录。

    • 如果当前数据库不存在，该命令将显示如下所示的错误： :code:` Cannot perform SHOW <object_type>.This session does not have a current database...`.

  • 如果您指定了未限定的 <table_name>，那么：

    • 如果有当前数据库和当前架构，该命令将检索当前数据库的当前架构中指定表的记录。

    • 如果没有当前数据库或没有当前架构，该命令将显示类似于以下这样的错误：:code:`SQL compilation error: <object> does not exist or not authorized. `。

If you specify the VIEW keyword or a view name, the rules for views parallel the rules for tables.

If you specify the APPLICATION or APPLICATION PACKAGE keywords, records for the specified Snowflake Native App Framework application or application package are returned.

默认：取决于会话当前是否正在使用数据库：

  • 数据库：DATABASE 是默认值（即该命令返回您有权在数据库中查看的对象）。

  • 无数据库：ACCOUNT 是默认值（即该命令返回您有权在账户中查看的对象）。

使用说明

  • You can use the VIEW keyword and specify a view name for standard views, materialized views, and semantic views.

  • 该命令 最多 返回指定对象类型的 1 万条记录，由用于执行命令的角色的访问权限决定。即使应用了筛选器，也不会返回任何超出 1 万条限制后的记录。

    要查看存在超过 1 万条记录的结果，请在 Snowflake Information Schema 中查询相应的视图（如果存在）。

  • 该命令不需要正在运行的仓库即可执行。

  • 该命令仅返回当前用户的当前角色已获授至少一项访问权限的对象。

  • MANAGE GRANTS 访问权限隐式允许其持有者查看账户中的每个对象。默认情况下，只有账户管理员（具有 ACCOUNTADMIN 角色的用户）和安全管理员（具有 SECURITYADMIN 角色的用户）才具有 MANAGE GRANTS 权限。

  • To post-process the output of this command, you can use the pipe operator (->>) or the RESULT_SCAN function. Both constructs treat the output as a result set that you can query.

    For example, you can use the pipe operator or RESULT_SCAN function to select specific columns from the SHOW command output or filter the rows.

    When you refer to the output columns, use double-quoted identifiers for the column names. For example, to select the output column type, specify SELECT "type".

    You must use double-quoted identifiers because the output column names for SHOW commands are in lowercase. The double quotes ensure that the column names in the SELECT list or WHERE clause match the column names in the SHOW command output that was scanned.

备注

The column names in the output table for the SHOW COLUMNS command are lowercase (that is, table_name, schema_name, column_name, and so on). However, the values in the column_name column reflect the column name that is stored. For example, if a column name is added without being enclosed in double quotes using the ALTER TABLE ... ADD COLUMN MYCOLUMN statement, the column name is stored in uppercase and appears as MYCOLUMN in the column_name column.

输出

命令输出在以下列中提供列属性和元数据：

列

描述

table_name

Name of the table or view that the column, dimension, fact, or metric belongs to.

schema_name

表的架构。

column_name

Name of the column, dimension, fact, or metric.

data_type

JSON object containing the data type and applicable properties of the column, dimension, fact, or metric.

The type key-value pair specifies the data type of the column, dimension, fact, or metric.

For string and numeric data types, type specifies one of the following values:

  • TEXT for all string types.

  • FIXED for all fixed-point numeric types.

  • REAL for all floating-point numeric types.

The other key-value pairs describe the properties that are applicable to the particular data type. For example:

  • If type is TEXT or BINARY, the additional key-value pairs can include length, byteLength, nullable, and fixed.

  • If type is FIXED, TIME, TIMESTAMP_NTZ, TIMESTAMP_LTZ, or TIMESTAMP_TZ, the additional key-value pairs can include precision, scale, and nullable.

  • If type is REAL, DATE, or BOOLEAN, the additional key-value pairs can include nullable.

null?

列是否可以包含 NULL 值。

default

为列定义的默认值（如果有）。

kind

One of the following values:

  • COLUMN for columns in tables, views, and materialized views.

  • DIMENSION for dimensions in semantic views.

  • FACT for facts in semantic views.

  • METRIC for metrics in semantic views.

expression

comment

Comment, if any, for the column, dimension, fact, or metric.

database_name

表的数据库。

autoincrement

列的自动递增起始值和增量值（如果有）。如果列具有 NOORDER 属性，则值包括 NOORDER （例如，IDENTITY START 1 INCREMENT 1 NOORDER）。否则，值包括 ORDER。

schema_evolution_record

记录特定表列最新触发的架构演进信息。此列包含以下子字段：

  • EvolutionType：触发的架构演进的类型（ADD_COLUMN 或 DROP_NOT_NULL）。

  • EvolutionMode: The triggering ingestion mechanism (COPY, SNOWPIPE, or SNOWPIPE_STREAMING).

  • FileName: The file name that triggered the evolution (NULL for SNOWPIPE_STREAMING).

  • TriggeringTime：列演化的大致时间。

  • QueryId or PipeId: A unique identifier of the triggering query or pipe (QUERY ID for COPY, PIPE ID for SNOWPIPE, or NULL for SNOWPIPE_STREAMING).

  • Pipe name: Fully qualified pipe name that triggered schema evolution (SNOWPIPE_STREAMING only).

  • Channel name: Channel that triggered schema evolution (SNOWPIPE_STREAMING only).

  • offsetTokenUpperBound: An offset at or before which schema evolution was triggered (SNOWPIPE_STREAMING only).

示例

The following example creates a table and then runs the SHOW COLUMNS command to list the columns in the table:

CREATE OR REPLACE TABLE test_show_columns (
  n1 NUMBER DEFAULT 5,
  n2_int INTEGER DEFAULT n1+5,
  n3_bigint BIGINT AUTOINCREMENT,
  n4_dec DECIMAL IDENTITY (1,10),
  f1 FLOAT,
  f2_double DOUBLE,
  f3_real REAL,
  s1 STRING,
  s2_var VARCHAR,
  s3_char CHAR,
  s4_text TEXT,
  "s5_case_sensitive" VARCHAR,
  b1 BINARY,
  b2_var VARBINARY,
  bool1 BOOLEAN,
  d1 DATE,
  t1 TIME,
  ts1 TIMESTAMP,
  ts2_ltz TIMESTAMP_LTZ,
  ts3_ntz TIMESTAMP_NTZ,
  ts4_tz TIMESTAMP_TZ);

SHOW COLUMNS IN TABLE test_show_columns;
Copy
+-------------------+----------------+-------------------+---------------------------------------------------------------------------------------+-------+--------------------------+--------+------------+---------+---------------+---------------------------------------+-------------------------+
| table_name        | schema_name    | column_name       | data_type                                                                             | null? | default                  | kind   | expression | comment | database_name | autoincrement                         | schema_evolution_record |
|-------------------+----------------+-------------------+---------------------------------------------------------------------------------------+-------+--------------------------+--------+------------+---------+---------------+---------------------------------------+-------------------------|
| TEST_SHOW_COLUMNS | MY_SCHEMA      | N1                | {"type":"FIXED","precision":38,"scale":0,"nullable":true}                             | true  | 5                        | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | N2_INT            | {"type":"FIXED","precision":38,"scale":0,"nullable":true}                             | true  | TEST_SHOW_COLUMNS.N1 + 5 | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | N3_BIGINT         | {"type":"FIXED","precision":38,"scale":0,"nullable":true}                             | true  |                          | COLUMN |            |         | MY_DB         | IDENTITY START 1 INCREMENT 1 NOORDER  | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | N4_DEC            | {"type":"FIXED","precision":38,"scale":0,"nullable":true}                             | true  |                          | COLUMN |            |         | MY_DB         | IDENTITY START 1 INCREMENT 10 NOORDER | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | F1                | {"type":"REAL","nullable":true}                                                       | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | F2_DOUBLE         | {"type":"REAL","nullable":true}                                                       | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | F3_REAL           | {"type":"REAL","nullable":true}                                                       | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | S1                | {"type":"TEXT","length":16777216,"byteLength":16777216,"nullable":true,"fixed":false} | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | S2_VAR            | {"type":"TEXT","length":16777216,"byteLength":16777216,"nullable":true,"fixed":false} | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | S3_CHAR           | {"type":"TEXT","length":1,"byteLength":4,"nullable":true,"fixed":false}               | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | S4_TEXT           | {"type":"TEXT","length":16777216,"byteLength":16777216,"nullable":true,"fixed":false} | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | s5_case_sensitive | {"type":"TEXT","length":16777216,"byteLength":16777216,"nullable":true,"fixed":false} | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | B1                | {"type":"BINARY","length":8388608,"byteLength":8388608,"nullable":true,"fixed":true}  | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | B2_VAR            | {"type":"BINARY","length":8388608,"byteLength":8388608,"nullable":true,"fixed":false} | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | BOOL1             | {"type":"BOOLEAN","nullable":true}                                                    | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | D1                | {"type":"DATE","nullable":true}                                                       | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | T1                | {"type":"TIME","precision":0,"scale":9,"nullable":true}                               | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | TS1               | {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}                      | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | TS2_LTZ           | {"type":"TIMESTAMP_LTZ","precision":0,"scale":9,"nullable":true}                      | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | TS3_NTZ           | {"type":"TIMESTAMP_NTZ","precision":0,"scale":9,"nullable":true}                      | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
| TEST_SHOW_COLUMNS | MY_SCHEMA      | TS4_TZ            | {"type":"TIMESTAMP_TZ","precision":0,"scale":9,"nullable":true}                       | true  |                          | COLUMN |            |         | MY_DB         |                                       | NULL                    |
+-------------------+----------------+-------------------+---------------------------------------------------------------------------------------+-------+--------------------------+--------+------------+---------+---------------+---------------------------------------+-------------------------+
语言: 中文