TYPES view¶

This Information Schema view displays a row for each user-defined type defined in the specified or current database.

Columns¶

Column name	Data type	Description
TYPE_CATALOG	VARCHAR	Database that contains the type.
TYPE_SCHEMA	VARCHAR	Schema that contains the type.
TYPE_NAME	VARCHAR	Name of the type.
TYPE_OWNER	VARCHAR	Name of the role that owns the type.
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	Creation time of the type.
LAST_ALTERED	TIMESTAMP_LTZ	Date and time the object was last altered by a DML, DDL, or background metadata operation. See `Usage Notes`_.
COMMENT	VARCHAR	Comment for this type.

The view only displays objects for which the current role for the session has been granted access privileges. The view doesn’t honor the MANAGE GRANTS privilege and consequently might show less information compared to a SHOW command when both are 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.

Retrieve all user-defined types in the mydb database:

SELECT type_name, type_owner, base_data_type
  FROM mydb.INFORMATION_SCHEMA.TYPES;

Retrieve all user-defined types in a specific schema:

SELECT type_name, type_owner, base_data_type
  FROM mydb.INFORMATION_SCHEMA.TYPES
  WHERE type_schema = 'MY_SCHEMA';