COLUMNS View

This Information Schema view displays a row for each column in the tables defined in the specified (or current) database.

See also:

DATABASES View

Columns

Column

Data Type

Description

TABLE_CATALOG

TEXT

Database that the table belongs to.

TABLE_SCHEMA

TEXT

Schema that the table belongs to.

TABLE_NAME

TEXT

Table or view that the column belongs to.

COLUMN_NAME

TEXT

Name of the column.

ORDINAL_POSITION

NUMBER

Ordinal position of the column in the table.

COLUMN_DEFAULT

TEXT

Default value of the column.

IS_NULLABLE

TEXT

‘YES’ if the column may contain NULL, ‘NO’ otherwise.

DATA_TYPE

TEXT

Data type of the column.

CHARACTER_MAXIMUM_LENGTH

NUMBER

Maximum length in characters of string columns.

CHARACTER_OCTET_LENGTH

NUMBER

Maximum length in bytes of string columns.

NUMERIC_PRECISION

NUMBER

Numeric precision of numeric columns.

NUMERIC_PRECISION_RADIX

NUMBER

Radix of precision of numeric columns.

NUMERIC_SCALE

NUMBER

Scale of numeric columns.

DATETIME_PRECISION

NUMBER

Not applicable for Snowflake.

INTERVAL_TYPE

TEXT

Not applicable for Snowflake.

INTERVAL_PRECISION

NUMBER

Not applicable for Snowflake.

CHARACTER_SET_CATALOG

TEXT

Not applicable for Snowflake.

CHARACTER_SET_SCHEMA

TEXT

Not applicable for Snowflake.

CHARACTER_SET_NAME

TEXT

Not applicable for Snowflake.

COLLATION_CATALOG

TEXT

Not applicable for Snowflake.

COLLATION_SCHEMA

TEXT

Not applicable for Snowflake.

COLLATION_NAME

TEXT

Not applicable for Snowflake.

DOMAIN_CATALOG

TEXT

Not applicable for Snowflake.

DOMAIN_SCHEMA

TEXT

Not applicable for Snowflake.

DOMAIN_NAME

TEXT

Not applicable for Snowflake.

UDT_CATALOG

TEXT

Not applicable for Snowflake.

UDT_SCHEMA

TEXT

Not applicable for Snowflake.

UDT_NAME

TEXT

Not applicable for Snowflake.

SCOPE_CATALOG

TEXT

Not applicable for Snowflake.

SCOPE_SCHEMA

TEXT

Not applicable for Snowflake.

SCOPE_NAME

TEXT

Not applicable for Snowflake.

MAXIMUM_CARDINALITY

NUMBER

Not applicable for Snowflake.

DTD_IDENTIFIER

TEXT

Not applicable for Snowflake.

IS_SELF_REFERENCING

TEXT

Not applicable for Snowflake.

IS_IDENTITY

TEXT

Whether this column is an identity column.

IDENTITY_GENERATION

TEXT

Whether an identity column’s value is always generated or only generated by default. Snowflake only supports BY DEFAULT.

IDENTITY_START

TEXT

The START value from CREATE TABLE ... (columnX ... AUTOINCREMENT START <#> ...).

IDENTITY_INCREMENT

TEXT

The INCREMENT value from CREATE TABLE ... (columnX ... AUTOINCREMENT INCREMENT <#> ...).

IDENTITY_MAXIMUM

TEXT

Not applicable for Snowflake.

IDENTITY_MINIMUM

TEXT

Not applicable for Snowflake.

IDENTITY_CYCLE

TEXT

Whether the value of an identity column may cycle. Snowflake only supports NO CYCLE.

IDENTITY_ORDERED

TEXT

If YES, the column is an identity column and has the ORDER property. If NO, the column is an identity column and has the NOORDER property.

SchemaEvolutionRecord

TEXT

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).

COMMENT

TEXT

Comment for this column.

Usage Notes

  • The view only displays objects for which the current role for the session has been granted access privileges. The view does not honor the MANAGE GRANTS privilege and consequently may show less information compared to a SHOW command when both are executed by a user who holds the MANAGE GRANTS privilege.

Examples

Retrieve all columns in the myTable table defined in the mydb database:

USE DATABASE mydb;
SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'myTable';
Copy