INFORMATION_SCHEMA.COLUMNS
The INFORMATION_SCHEMA.COLUMNS view contains metadata for all the columns within the tables and views in a project.
SyntaxSELECT *
FROM INFORMATION_SCHEMA.COLUMNS
note
-
The list of columns for tables is determined by the metadata caching policy on individual sources. The columns are not refreshed if the data is expired.
-
You may not see some rows in the table if you do not have access to the metadata storage container.
Example Output
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | COLUMN_SIZE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_PRECISION_RADIX | NUMERIC_SCALE | DATETIME_PRECISION | INTERVAL_TYPE | INTERVAL_PRECISION |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| DREMIO | INFORMATION_SCHEMA | CATALOGS | CATALOG_NAME | 1 | null | YES | CHARACTER VARYING | 65536 | 65536 | 262144 | null | null | null | null | null | null |
| DREMIO | INFORMATION_SCHEMA | CATALOGS | CATALOG_DESCRIPTION | 2 | null | YES | CHARACTER VARYING | 65536 | 65536 | 262144 | null | null | null | null | null | null |
| DREMIO | INFORMATION_SCHEMA | CATALOGS | CATALOG_CONNECT | 3 | null | YES | CHARACTER VARYING | 65536 | 65536 | 262144 | null | null | null | null | null | null |
| DREMIO | INFORMATION_SCHEMA | COLUMNS | TABLE_CATALOG | 1 | null | YES | CHARACTER VARYING | 65536 | 65536 | 262144 | null | null | null | null | null | null |
| DREMIO | INFORMATION_SCHEMA | COLUMNS | TABLE_SCHEMA | 2 | null | YES | CHARACTER VARYING | 65536 | 65536 | 262144 | null | null | null | null | null | null |
| DREMIO | INFORMATION_SCHEMA | COLUMNS | TABLE_NAME | 3 | null | YES | CHARACTER VARYING | 65536 | 65536 | 262144 | null | null | null | null | null | null |
| DREMIO | INFORMATION_SCHEMA | COLUMNS | COLUMN_NAME | 4 | null | YES | CHARACTER VARYING | 65536 | 65536 | 262144 | null | null | null | null | null | null |
| DREMIO | INFORMATION_SCHEMA | COLUMNS | ORDINAL_POSITION | 5 | null | YES | INTEGER | 32 | null | null | 32 | 2 | 0 | null | null | null |
| DREMIO | INFORMATION_SCHEMA | COLUMNS | COLUMN_DEFAULT | 6 | null | YES | CHARACTER VARYING | 65536 | 65536 | 262144 | null | null | null | null | null | null |
| DREMIO | INFORMATION_SCHEMA | COLUMNS | IS_NULLABLE | 7 | null | YES | CHARACTER | VARYING | 65536 | 65536 | 262144 | null | null | null | null | null |
Fields
| Field | Data Type | Description |
|---|---|---|
| TABLE_CATALOG | varchar | The name of the catalog, which is always DREMIO. |
| TABLE_SCHEMA | varchar | The path (source, space, folders) to the table or view. |
| TABLE_NAME | varchar | The name of the table or view that the column belongs to. |
| COLUMN_NAME | varchar | The name of the column in the table or view. |
| ORDINAL_POSITION | integer | This represents the position at which the column appears in the table or view. |
| COLUMN_DEFAULT | varchar | The default value of the column. |
| IS_NULLABLE | varchar | The value is YES if null values can be stored in the column and the value is NO if null values cannot be stored in the column. |
| DATA_TYPE | varchar | The system-defined data type of the column in the table or view. You can also view that specific dataset to see the data types for all columns. |
| COLUMN_SIZE | integer | The size of the table or view column in bytes. |
| CHARACTER_MAXIMUM_LENGTH | integer | The maximum length in characters for binary data, character data, or text and image data. Otherwise, null is returned. |
| CHARACTER_OCTET_LENGTH | integer | The maximum length in bytes for binary data, character data, or text and image data. Otherwise, null is returned. |
| NUMERIC_PRECISION | integer | The precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, null is returned. |
| NUMERIC_PRECISION_RADIX | integer | The precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, null is returned. |
| NUMERIC_SCALE | integer | The scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, null is returned. |
| DATETIME_PRECISION | integer | The supported precision for datetime and interval data types. For other data types, null is returned. |
| INTERVAL_TYPE | integer | If the data type is interval, then specified fields (year) are returned. Otherwise null is returned. |
| INTERVAL_PRECISION | integer | If the data type is interval, then the declared precision is displayed. Otherwise null is returned. |