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