Metadata views: User-defined types now surfaced in INFORMATION_SCHEMA, ACCOUNT_USAGE, and ORGANIZATION_USAGE (Pending)¶

When this behavior change bundle is enabled, objects that reference a user-defined type are represented by their type name (and as first-class TYPE grantables) across the INFORMATION_SCHEMA, ACCOUNT_USAGE, and ORGANIZATION_USAGE metadata views. The following views are affected:

• COLUMNS: INFORMATION_SCHEMA, ACCOUNT_USAGE, and ORGANIZATION_USAGE
• FUNCTIONS: INFORMATION_SCHEMA, ACCOUNT_USAGE, and ORGANIZATION_USAGE
• PROCEDURES: INFORMATION_SCHEMA
• OBJECT_PRIVILEGES: INFORMATION_SCHEMA
• GRANTS_TO_ROLES: ACCOUNT_USAGE and ORGANIZATION_USAGE

Before the change:

• In the COLUMNS views, a column typed as a user-defined type reports its underlying base type (or a generic USER_DEFINED_TYPE or internal encoded name) in DATA_TYPE, not the type’s name.
• In the FUNCTIONS and PROCEDURES views, ARGUMENT_SIGNATURE and DATA_TYPE (the return type) show the base type instead of the user-defined type name.
• The INFORMATION_SCHEMA.OBJECT_PRIVILEGES view doesn’t return any rows for user-defined types (no rows with OBJECT_TYPE = 'TYPE').
• The GRANTS_TO_ROLES views don’t return grant rows for user-defined types.

After the change:

• In the COLUMNS views, DATA_TYPE displays the user-defined type name for columns of that type.
• In the FUNCTIONS and PROCEDURES views, ARGUMENT_SIGNATURE and DATA_TYPE display user-defined type names.
• The INFORMATION_SCHEMA.OBJECT_PRIVILEGES view returns rows with OBJECT_TYPE = 'TYPE' for privileges granted on user-defined types.
• The GRANTS_TO_ROLES views return rows with TYPE = 'TYPE' for user-defined type grants.

Example¶

Create a user-defined type and a table that uses it, then query the COLUMNS view:

CREATE TYPE my_number_type AS NUMBER;   -- a user-defined type
CREATE TABLE t (c my_number_type);

SELECT column_name, data_type
  FROM information_schema.columns
  WHERE table_name = 'T';

Before the change, DATA_TYPE reports the underlying base type:

COLUMN_NAME | DATA_TYPE
------------+----------------
C           | NUMBER

After the change, DATA_TYPE reports the user-defined type name:

COLUMN_NAME | DATA_TYPE
------------+----------------
C           | MY_NUMBER_TYPE

This change is mostly additive, but you can observe it. Tooling that parses DATA_TYPE or ARGUMENT_SIGNATURE now sees user-defined type names where it previously saw base types, and automation that counts or compares rows in these views sees new rows for user-defined type columns, privileges (OBJECT_TYPE = 'TYPE'), and grants (TYPE = 'TYPE'). If you have downstream pipelines with hard-coded type strings or row-count expectations, update them before the bundle is enabled.

Ref: 2380