Schema:

ORGANIZATION_USAGE

COLUMNS view¶

Important

This view is only available in the organization account. For more information, see Premium views in the organization account.

This Organization Usage view displays a row for each column in the tables defined in an account.

See also:

DATABASES view

Columns¶

Organization-level columns

Column Name

Data Type

Description

ORGANIZATION_NAME

VARCHAR

Name of the organization.

ACCOUNT_LOCATOR

VARCHAR

System-generated identifier for the account.

ACCOUNT_NAME

VARCHAR

User-defined identifier for the account.

Additional columns

Column

Data Type

Description

COLUMN_ID

NUMBER

Internal/system-generated identifier for the column.

COLUMN_NAME

TEXT

Name of the column.

TABLE_ID

NUMBER

Internal/system-generated identifier for the table or view for the column.

TABLE_NAME

TEXT

Table or view that the column belongs to.

TABLE_SCHEMA_ID

NUMBER

Internal/system-generated identifier for the schema of the table or view for the column.

TABLE_SCHEMA

TEXT

Schema that the table or view belongs to.

TABLE_CATALOG_ID

NUMBER

Internal/system-generated identifier for the database of the table or view for the column.

TABLE_CATALOG

TEXT

Database that the table or view belongs to.

ORDINAL_POSITION

NUMBER

Ordinal position of the column in the table/view.

COLUMN_DEFAULT

TEXT

Default value of the column.

IS_NULLABLE

TEXT

Whether the column allows NULL values.

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

TEXT

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

TEXT

Not applicable for Snowflake.

DTD_IDENTIFIER

TEXT

Not applicable for Snowflake.

IS_SELF_REFERENCING

TEXT

Not applicable for Snowflake.

IS_IDENTITY

TEXT

Whether the 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

Not applicable for Snowflake.

IDENTITY_INCREMENT

TEXT

Not applicable for Snowflake.

IDENTITY_MAXIMUM

TEXT

Not applicable for Snowflake.

IDENTITY_MINIMUM

TEXT

Not applicable for Snowflake.

IDENTITY_CYCLE

TEXT

Whether the value of an identity column allows cycling. 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 the column.

DELETED

TIMESTAMP_LTZ

Date and time when the column was deleted.

Usage notes¶

  • Latency for the view may be up to 24 hours.

  • The view does not recognize the MANAGE GRANTS privilege and consequently may show less information compared to a SHOW command executed by a user who holds the MANAGE GRANTS privilege.

Examples¶

The following example retrieves all columns in the myTable table defined in the mydb database:

SELECT *
  FROM snowflake.organization_usage.columns
  WHERE
    table_catalog = 'mydb' AND
    table_name = 'myTable' AND
    deleted IS NULL;
Copy
Language: English