Categories:
Table, View, & Sequence DDL

DESCRIBE TABLE

Describes either the columns in a table or the current values, as well as the default values, for the stage properties for a table.

DESCRIBE can be abbreviated to DESC.

See also:

ALTER TABLE , CREATE TABLE , SHOW TABLES

DESCRIBE VIEW

Syntax

DESC[RIBE] 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 stage properties (including their current and default values) for the table.

Default: TYPE = COLUMNS

Usage Notes

  • This command does not show the object parameters for a table. Instead, use SHOW PARAMETERS IN TABLE ….
  • DESC TABLE and DESCRIBE VIEW are interchangeable. Either command retrieves the details for the table or view that matches the criteria in the statement; however, TYPE = STAGE does not apply for views because views do not have stage properties.
  • 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.

Examples

Create an example table:

CREATE TABLE emp (id NUMBER NOT NULL PRIMARY KEY, fname VARCHAR(50), lname VARCHAR(50), location VARCHAR(100));

Describe the columns in the table:

DESC TABLE emp;

+----------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name     | type         | kind   | null? | default | primary key | unique key | check | expression | comment |
|----------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| ID       | NUMBER(38,0) | COLUMN | N     | NULL    | Y           | N          | NULL  | NULL       | NULL    |
| FNAME    | VARCHAR(50)  | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| LNAME    | VARCHAR(50)  | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| LOCATION | VARCHAR(100) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+----------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+

Describe the stage properties for the table:

DESC TABLE emp 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                |
| STAGE_FILE_FORMAT  | DATE_FORMAT                    | String        | AUTO            | AUTO             |
| STAGE_FILE_FORMAT  | TIME_FORMAT                    | String        | AUTO            | AUTO             |
| STAGE_FILE_FORMAT  | TIMESTAMP_FORMAT               | String        | AUTO            | AUTO             |
| STAGE_FILE_FORMAT  | BINARY_FORMAT                  | String        | HEX             | HEX              |
| STAGE_FILE_FORMAT  | ESCAPE                         | String        | NONE            | NONE             |
| STAGE_FILE_FORMAT  | ESCAPE_UNENCLOSED_FIELD        | String        | \\              | \\               |
| STAGE_FILE_FORMAT  | TRIM_SPACE                     | Boolean       | false           | false            |
| STAGE_FILE_FORMAT  | FIELD_OPTIONALLY_ENCLOSED_BY   | String        | NONE            | NONE             |
| STAGE_FILE_FORMAT  | NULL_IF                        | List          | [\\N]           | [\\N]            |
| STAGE_FILE_FORMAT  | COMPRESSION                    | String        | AUTO            | AUTO             |
| STAGE_FILE_FORMAT  | ERROR_ON_COLUMN_COUNT_MISMATCH | Boolean       | true            | true             |
| STAGE_FILE_FORMAT  | VALIDATE_UTF8                  | Boolean       | true            | true             |
| STAGE_FILE_FORMAT  | EMPTY_FIELD_AS_NULL            | Boolean       | true            | true             |
| STAGE_FILE_FORMAT  | SKIP_BYTE_ORDER_MARK           | Boolean       | true            | true             |
| STAGE_FILE_FORMAT  | ENCODING                       | String        | UTF8            | UTF8             |
| STAGE_COPY_OPTIONS | ON_ERROR                       | String        | ABORT_STATEMENT | ABORT_STATEMENT  |
| STAGE_COPY_OPTIONS | SIZE_LIMIT                     | Long          |                 |                  |
| STAGE_COPY_OPTIONS | PURGE                          | Boolean       | false           | false            |
| STAGE_COPY_OPTIONS | RETURN_FAILED_ONLY             | Boolean       | false           | false            |
| STAGE_COPY_OPTIONS | ENFORCE_LENGTH                 | Boolean       | true            | true             |
| STAGE_COPY_OPTIONS | TRUNCATECOLUMNS                | Boolean       | false           | false            |
| STAGE_COPY_OPTIONS | FORCE                          | Boolean       | false           | false            |
| STAGE_LOCATION     | URL                            | String        |                 |                  |
+--------------------+--------------------------------+---------------+-----------------+------------------+