Pular para o conteúdo principal

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

Always NULL, reserved for future use.

IS_NULLABLE

STRING

No

Yes

YES if column is nullable, NO otherwise.

FULL_DATA_TYPE

STRING

No

No

The data type as specified in the column definition.

DATA_TYPE

STRING

No

Yes

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

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​

SQL
-- 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.columns
    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
Última atualização em