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.
- See also:
Syntax¶
{ DESCRIBE | DESC } TABLE <name> [ TYPE = { COLUMNS | STAGE } ]
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 of the column in the table. |
|
Data type of the column in the table. |
|
This value is always |
|
Whether the column accepts NULL values ( |
|
The default value for the column, if any (otherwise |
|
Whether the column is the primary key (or part of a multi-column primary key; |
|
Whether the column has a UNIQUE constraint ( |
|
Reserved for future use. |
|
Reserved for future use. |
|
The comment set for the column, if any (otherwise |
|
The masking policy set for the column, if any (otherwise |
|
The privacy domain set for the column, if any (otherwise |
|
Records information about the latest triggered Schema Evolution for a given table column. This column contains the following subfields:
|
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));
Describe the columns in the table:
DESCRIBE TABLE desc_example;
+------+--------------+--------+-------+------------------+-------------+------------+-------+------------+---------------------------------+-------------+----------------+-------------------------+
| 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);
+------+--------------+--------+-------+------------------+-------------+------------+-------+------------+---------------------------------+---------------------------------+----------------+-------------------------+
| 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;
+--------------------+--------------------------------+---------------+-----------------+------------------+
| 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 |
...