DESCRIBE TABLE¶

Describes either the columns in a table or the set of stage properties for the table (current values and default values).

DESCRIBE can be abbreviated to DESC.

DESCRIBE VIEW

Syntax¶

{ DESCRIBE | DESC } TABLE <name> [ TYPE =  { COLUMNS | STAGE } ]

Copy

Parameters¶

name

Specifies the identifier for the table to describe. If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

TYPE = COLUMNS | STAGE

Specifies whether to display the columns for the table or the set of stage properties for the table (current values and default values).

Default: TYPE = COLUMNS

Usage notes¶

This command does not show the object parameters for a table. Instead, use SHOW PARAMETERS IN TABLE.
DESCRIBE TABLE and DESCRIBE VIEW are interchangeable. Both commands return details for the specified table or view; however, TYPE = STAGE does not apply for views because views do not have stage properties.
If schema evolution is enabled on the table, the output contains a SchemaEvolutionRecord column. This column was introduced with the 2023_08 Bundle (Generally Enabled). For more information, see Table schema evolution.
The output includes a policy name column to indicate the masking policy set on the column.

If a masking policy is not set on the column or if the Snowflake account is not Enterprise Edition or higher, Snowflake returns NULL.
The output includes a privacy domain column to indicate the privacy domain set on the column.

If a privacy domain is not set on the column or if the Snowflake account is not Enterprise Edition or higher, Snowflake returns NULL.

To post-process the output of this command, you can use the RESULT_SCAN function, which treats the output as a table that can be queried.

Output¶

When TYPE = COLUMNS, the command output provides the following properties and metadata:

Column	Description
`name`	Name of the column in the table.
`type`	Data type of the column in the table. If collation has been specified for the column, the collation specification is included.
`kind`	This value is always `COLUMN` for Snowflake tables.
`null?`	Whether the column accepts NULL values (`Y` or `N`).
`default`	The default value for the column, if any (otherwise `NULL`).
`primary key`	Whether the column is the primary key (or part of a multi-column primary key; `Y` or `N`).
`unique key`	Whether the column has a UNIQUE constraint (`Y` or `N`).
`check`	Reserved for future use.
`expression`	Reserved for future use.
`comment`	The comment set for the column, if any (otherwise `NULL`).
`policy name`	The masking policy set for the column, if any (otherwise `NULL`).
`privacy domain`	The privacy domain set for the column, if any (otherwise `NULL`).
`schema evolution record`	Records information about the latest triggered Schema Evolution for a given table column. This column contains the following subfields: EvolutionType: The type of the triggered schema evolution (ADD_COLUMN or DROP_NOT_NULL). EvolutionMode: The triggering ingestion mechanism (COPY or SNOWPIPE). FileName: The file name that triggered the evolution. TriggeringTime: The approximate time when the column was evolved. QueryId or PipeID: A unique identifier of the triggering query or pipe (QUERY ID for COPY or PIPE ID for SNOWPIPE).

When TYPE = STAGE, the command output provides the current and default values for the table’s stage properties. See Example: Describe stage properties.

Examples¶

The following examples show how to describe tables.

Example: Describe a table that has constraints and other column attributes¶

Create a table with five columns, two with constraints. Give one column a DEFAULT value and a comment.

CREATE OR REPLACE TABLE desc_example(
  c1 INT PRIMARY KEY,
  c2 INT,
  c3 INT UNIQUE,
  c4 VARCHAR(30) DEFAULT 'Not applicable' COMMENT 'This column is rarely populated',
  c5 VARCHAR(100));

Copy

Describe the columns in the table:

DESCRIBE TABLE desc_example;

Copy

+------+--------------+--------+-------+------------------+-------------+------------+-------+------------+---------------------------------+-------------+----------------+-------------------------+
| name | type         | kind   | null? | default          | primary key | unique key | check | expression | comment                         | policy name | privacy domain | schema evolution record |
|------+--------------+--------+-------+------------------+-------------+------------+-------+------------+---------------------------------+-------------+----------------+-------------------------|
| C1   | NUMBER(38,0) | COLUMN | N     | NULL             | Y           | N          | NULL  | NULL       | NULL                            | NULL        | NULL           | NULL                    |
| C2   | NUMBER(38,0) | COLUMN | Y     | NULL             | N           | N          | NULL  | NULL       | NULL                            | NULL        | NULL           | NULL                    |
| C3   | NUMBER(38,0) | COLUMN | Y     | NULL             | N           | Y          | NULL  | NULL       | NULL                            | NULL        | NULL           | NULL                    |
| C4   | VARCHAR(30)  | COLUMN | Y     | 'Not applicable' | N           | N          | NULL  | NULL       | This column is rarely populated | NULL        | NULL           | NULL                    |
| C5   | VARCHAR(100) | COLUMN | Y     | NULL             | N           | N          | NULL  | NULL       | NULL                            | NULL        | NULL           | NULL                    |
+------+--------------+--------+-------+------------------+-------------+------------+-------+------------+---------------------------------+-------------+----------------+-------------------------+

Example: Describe a table that has a masking policy on a column¶

Create a normal masking policy, then recreate the desc_example table with the masking policy set on one column. (To run this example, create the email_mask masking policy first.)

CREATE OR REPLACE TABLE desc_example(
  c1 INT PRIMARY KEY,
  c2 INT,
  c3 INT UNIQUE,
  c4 VARCHAR(30) DEFAULT 'Not applicable' COMMENT 'This column is rarely populated',
  c5 VARCHAR(100) WITH MASKING POLICY email_mask);

Copy

+------+--------------+--------+-------+------------------+-------------+------------+-------+------------+---------------------------------+---------------------------------+----------------+-------------------------+
| name | type         | kind   | null? | default          | primary key | unique key | check | expression | comment                         | policy name                     | privacy domain | schema evolution record |
|------+--------------+--------+-------+------------------+-------------+------------+-------+------------+---------------------------------+---------------------------------+----------------|-------------------------|
| C1   | NUMBER(38,0) | COLUMN | N     | NULL             | Y           | N          | NULL  | NULL       | NULL                            | NULL                            | NULL           | NULL                    |
| C2   | NUMBER(38,0) | COLUMN | Y     | NULL             | N           | N          | NULL  | NULL       | NULL                            | NULL                            | NULL           | NULL                    |
| C3   | NUMBER(38,0) | COLUMN | Y     | NULL             | N           | Y          | NULL  | NULL       | NULL                            | NULL                            | NULL           | NULL                    |
| C4   | VARCHAR(30)  | COLUMN | Y     | 'Not applicable' | N           | N          | NULL  | NULL       | This column is rarely populated | NULL                            | NULL           | NULL                    |
| C5   | VARCHAR(100) | COLUMN | Y     | NULL             | N           | N          | NULL  | NULL       | NULL                            | HT_SENSORS.HT_SCHEMA.EMAIL_MASK | NULL           | NULL                    |
+------+--------------+--------+-------+------------------+-------------+------------+-------+------------+---------------------------------+---------------------------------+----------------+-------------------------+

Example: Describe stage properties¶

Describe the current stage properties for the same table (only the first five rows are shown here):

DESCRIBE TABLE desc_example TYPE = STAGE;

Copy

+--------------------+--------------------------------+---------------+-----------------+------------------+
| parent_property    | property                       | property_type | property_value  | property_default |
|--------------------+--------------------------------+---------------+-----------------+------------------|
| STAGE_FILE_FORMAT  | TYPE                           | String        | CSV             | CSV              |
| STAGE_FILE_FORMAT  | RECORD_DELIMITER               | String        | \n              | \n               |
| STAGE_FILE_FORMAT  | FIELD_DELIMITER                | String        | ,               | ,                |
| STAGE_FILE_FORMAT  | FILE_EXTENSION                 | String        |                 |                  |
| STAGE_FILE_FORMAT  | SKIP_HEADER                    | Integer       | 0               | 0                |
...