COLUMNS

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.4 LTS and above check marked yes Unity Catalog only

INFORMATION_SCHEMA.COLUMNS describes columns of tables and views (relations) in the catalog.

The rows returned are limited to the relations the user is privileged to interact with.

Definition

The COLUMNS relation contains the following columns:

Name

Data type

Nullable

Standard

Description

TABLE_CATALOG

STRING

No

Yes

Catalog that contains the relation.

TABLE_SCHEMA

STRING

No

Yes

Schema that contains the relation.

TABLE_NAME

STRING

No

Yes

Name of the relation the column is part of.

COLUMN_NAME

STRING

No

Yes

Name of the column.

ORDINAL_POSITION

INTEGER

No

Yes

The position (numbered from 1) of the column within the relation.

COLUMN_DEFAULT

STRING

No

Yes

The default value used when the column is not specified in an INSERT, NULL if undefined.

IS_NULLABLE

STRING

No

Yes

YES if column is nullable, NO otherwise.

DATA_TYPE

STRING

No

Yes

The simple data type name of the column, or STRUCT, or ARRAY.

FULL_DATA_TYPE

STRING

No

No

The data type as specified in the column definition.

CHARACTER_MAXIMUM_LENGTH

INTEGER

Yes

Yes

Always NULL, reserved for future use.

CHARACTER_OCTET_LENGTH

STRING

Yes

Yes

Always NULL, reserved for future use.

NUMERIC_PRECISION

INTEGER

Yes

Yes

For base-2 integral numeric types, FLOAT, and DOUBLE, the number of supported bits. For DECIMAL the number of digits, NULL otherwise.

NUMERIC_PRECISION_RADIX

INTEGER

Yes

Yes

For DECIMAL 10, for all other numeric types 2, NULL otherwise.

NUMERIC_SCALE

INTEGER

Yes

Yes

For integral numeric types 0, for DECIMAL the number of digits to the right of the decimal point, NULL otherwise.

DATETIME_PRECISION

INTEGER

Yes

Yes

For DATE 0, for TIMESTAMP, and INTERVAL … SECOND 3, any other INTERVAL 0, NULL otherwise.

INTERVAL_TYPE

STRING

Yes

Yes

For INTERVAL the unit portion of the interval, e.g. 'YEAR TO MONTH', NULL otherwise.

INTERVAL_PRECISION

INTERAL

Yes

Yes

Always NULL, reserved for future use.

MAXIMUM_CARDINALITY

INTEGER

Yes

Yes

Always NULL, reserved for future use.

IS_IDENTITY

STRING

No

Yes

Always ‘NO’, reserved for future use.

IDENTITY_GENERATION

STRING

Yes

Yes

Always NULL, reserved for future use.

IDENTITY_START

STRING

Yes

Yes

Always NULL, reserved for future use.

IDENTITY_INCREMENT

STRING

Yes

Yes

Always NULL, reserved for future use.

IDENTITY_MAXIMUM

STRING

Yes

Yes

Always NULL, reserved for future use.

IDENTITY_MINIMUM

STRING

Yes

Yes

Always NULL, reserved for future use.

IDENTITY_CYCLE

STRING

Yes

Yes

Always NULL, reserved for future use.

IS_GENERATED

STRING

Yes

Yes

Always NULL, reserved for future use.

GENERATION_EXPRESSION

STRING

Yes

Yes

Always NULL, reserved for future use.

IS_SYSTEM_TIME_PERIOD_START

STRING

No

Yes

Always NO, reserved for future use.

IS_SYSTEM_TIME_PERIOD_END

STRING

No

Yes

Always NO, reserved for future use.

SYSTEM_TIME_PERIOD_TIMESTAMP_GENERATION

STRING

Yes

Yes

Always NULL, reserved for future use.

IS_UPDATABLE

STRING

No

Yes

YES if column is updatable, NO otherwise.

PARTITION_ORDINAL_POSITION

INTEGER

Yes

No

Position (numbered from 1) of the column in the partition, NULL if not a partitioning column.

COMMENT

STRING

Yes

No

Optional description of the column.

Constraints

The following constraints apply to the COLUMNS relation:

Class

Name

Column List

Description

Primary key

COLUMNS_PK

TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME

Unique identifier for the column.

Unique key

COLUMNS_UK

TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION)

Unique identifier the column.

Foreign key

COLUMN_TABLES_FK

TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME

References TABLES.

Examples 

-- Show the columns of the CATALOG_PRIVILEGES relation in the main.information_schema schema.
> SELECT ordinal_position, column_name, data_type
    FROM main.information_schema.tables
    WHERE table_schema = 'information_schema'
      AND table_name = 'catalog_privileges'
    ORDER BY ordinal_position;
  1  grantor        STRING
  2  grantee        STRING
  3  catalog_name   STRING
  4  privilege_type STRING
  5  is_grantable   STRING