COLUMNS

  • Article

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