TYPES view¶

Important

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

Organization Usage performance

When you query a specific view in the SNOWFLAKE.ORGANIZATION_USAGE schema, follow the organization-wide guidance in Performance (Organization Usage): bound every scan on history views, list columns explicitly, and use the time filter column table plus worked SQL and anti-patterns there.

This Organization Usage view displays a row for each user-defined type defined in an account.

See also:: TYPES view (Information Schema) , TYPES view (Account Usage)

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 name	Data type	Description
TYPE_ID	NUMBER	Internal/system-generated identifier for the type.
TYPE_NAME	VARCHAR	Name of the type.
TYPE_SCHEMA_ID	NUMBER	Internal/system-generated identifier for the schema that contains the type.
TYPE_SCHEMA	VARCHAR	Schema that contains the type.
TYPE_CATALOG_ID	NUMBER	Internal/system-generated identifier for the database that contains the type.
TYPE_CATALOG	VARCHAR	Database that contains the type.
TYPE_OWNER	VARCHAR	Name of the role that owns the type.
OWNER_ROLE_TYPE	VARCHAR	The type of role that owns the object, for example `ROLE`. If a Snowflake Native App owns the object, the value is `APPLICATION`. Snowflake returns NULL if you delete the object because a deleted object does not have an owner role.
BASE_DATA_TYPE	VARCHAR	Underlying data type of the user-defined type.
CHARACTER_MAXIMUM_LENGTH	NUMBER	Maximum length in characters for VARCHAR types.
CHARACTER_OCTET_LENGTH	NUMBER	Maximum length in bytes for VARCHAR types.
NUMERIC_PRECISION	NUMBER	Numeric precision for NUMBER types.
NUMERIC_PRECISION_RADIX	NUMBER	Radix of the numeric precision for NUMBER types.
NUMERIC_SCALE	NUMBER	Numeric scale for NUMBER types.
DATETIME_PRECISION	NUMBER	Fractional seconds precision for TIMESTAMP types.
CHECK_EXPRESSION	VARCHAR	Not applicable for Snowflake.
DEFAULT_EXPRESSION	VARCHAR	Not applicable for Snowflake.
IS_NULLABLE_DEFAULT	VARCHAR	Not applicable for Snowflake.
COLLATION_NAME	VARCHAR	Not applicable for Snowflake.
CREATED	TIMESTAMP_LTZ	Date and time when the type was created.
LAST_ALTERED	TIMESTAMP_LTZ	Date and time the object was last altered by a DML, DDL, or background metadata operation. See `Usage Notes`_.
DELETED	TIMESTAMP_LTZ	Date and time when the type was dropped.
COMMENT	VARCHAR	Comment for this type.

Usage notes¶

Latency for the view might be up to 24 hours.
The view only displays objects for which the current role for the session has been granted access privileges.
The view doesn’t recognize the MANAGE GRANTS privilege and consequently might show less information compared to a SHOW command executed by a user who holds the MANAGE GRANTS privilege.
The LAST_ALTERED column is updated when the following operations are performed on an object:
- DDL operations.
- DML operations (for tables only). This column is updated even when no rows are affected by the DML statement.
- Background maintenance operations on metadata performed by Snowflake.

Examples¶

Retrieve all user-defined types in the organization:

SELECT type_name, type_catalog, type_schema, type_owner, base_data_type
  FROM SNOWFLAKE.ORGANIZATION_USAGE.TYPES
  ORDER BY created DESC;

Retrieve user-defined types that have been dropped:

SELECT type_name, type_catalog, type_schema, deleted
  FROM SNOWFLAKE.ORGANIZATION_USAGE.TYPES
  WHERE deleted IS NOT NULL
  ORDER BY deleted DESC;