COLUMNS

Applies to: Databricks SQL Databricks Runtime 10.4 LTS and above 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	INTEGER	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_INDEX	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.

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	INTEGER	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_INDEX	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 for the column.
Foreign key	COLUMN_TABLES_FK	TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME	References TABLES.

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 for 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

Related

• DESCRIBE TABLE
• Information schema
• INFORMATION_SCHEMA.TABLES
• SHOW COLUMNS
• SHOW TABLE
• SHOW TABLES