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