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. This column shows the standard Snowflake data type of the column. The DATA_TYPE_ALIAS column displays the original data type name that was specified for the column when the table was created, or when the column was altered.
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.
SCHEMA_EVOLUTION_RECORD	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, SNOWPIPE, or SNOWPIPE_STREAMING). FileName: The file name that triggered the evolution (NULL for SNOWPIPE_STREAMING). 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, PIPE ID for SNOWPIPE, or NULL for SNOWPIPE_STREAMING). Pipe name: Fully qualified pipe name that triggered schema evolution (SNOWPIPE_STREAMING only). Channel name: Channel that triggered schema evolution (SNOWPIPE_STREAMING only). offsetTokenUpperBound: An offset at or before which schema evolution was triggered (SNOWPIPE_STREAMING only).
COMMENT	TEXT	Comment for this column.
DATA_TYPE_ALIAS	TEXT	The data type alias or synonym specified for the column when the table was created or when the column was last altered. For example, the BIGINT type is synonymous with the NUMBER type. If BIGINT was specified as the type for a column, then BIGINT is displayed in this DATA_TYPE_ALIAS column. For columns in tables that were created before the 2025_07 behavior change bundle was enabled, and not altered after the behavior change, the value in this column is NULL. For more information, see COLUMNS view (multiple schemas): New 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