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 | 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
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 pipe operator or the RESULT_SCAN function. Both constructs treat the output as a result set that you can query.
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.
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 | ...
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4