Skip to main content
Version: 24.3.x

INFORMATION_SCHEMA.COLUMNS

The INFORMATION_SCHEMA.COLUMNS view contains metadata for all the columns within the tables and views in a project.

Syntax
SELECT * 
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.

Example Output

TABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEORDINAL_POSITIONCOLUMN_DEFAULTIS_NULLABLEDATA_TYPECOLUMN_SIZECHARACTER_MAXIMUM_LENGTHCHARACTER_OCTET_LENGTHNUMERIC_PRECISIONNUMERIC_PRECISION_RADIXNUMERIC_SCALEDATETIME_PRECISIONINTERVAL_TYPEINTERVAL_PRECISION
DREMIOINFORMATION_SCHEMACATALOGSCATALOG_NAME1nullYESCHARACTER VARYING6553665536262144nullnullnullnullnullnull
DREMIOINFORMATION_SCHEMACATALOGSCATALOG_DESCRIPTION2nullYESCHARACTER VARYING6553665536262144nullnullnullnullnullnull
DREMIOINFORMATION_SCHEMACATALOGSCATALOG_CONNECT3nullYESCHARACTER VARYING6553665536262144nullnullnullnullnullnull
DREMIOINFORMATION_SCHEMACOLUMNSTABLE_CATALOG1nullYESCHARACTER VARYING6553665536262144nullnullnullnullnullnull
DREMIOINFORMATION_SCHEMACOLUMNSTABLE_SCHEMA2nullYESCHARACTER VARYING6553665536262144nullnullnullnullnullnull
DREMIOINFORMATION_SCHEMACOLUMNSTABLE_NAME3nullYESCHARACTER VARYING6553665536262144nullnullnullnullnullnull
DREMIOINFORMATION_SCHEMACOLUMNSCOLUMN_NAME4nullYESCHARACTER VARYING6553665536262144nullnullnullnullnullnull
DREMIOINFORMATION_SCHEMACOLUMNSORDINAL_POSITION5nullYESINTEGER32nullnull3220nullnullnull
DREMIOINFORMATION_SCHEMACOLUMNSCOLUMN_DEFAULT6nullYESCHARACTER VARYING6553665536262144nullnullnullnullnullnull
DREMIOINFORMATION_SCHEMACOLUMNSIS_NULLABLE7nullYESCHARACTERVARYING6553665536262144nullnullnullnullnull

Fields

FieldData TypeDescription
TABLE_CATALOGvarcharThe name of the catalog, which is always DREMIO.
TABLE_SCHEMAvarcharThe path (source, space, folders) to the table or view.
TABLE_NAMEvarcharThe name of the table or view that the column belongs to.
COLUMN_NAMEvarcharThe name of the column in the table or view.
ORDINAL_POSITIONintegerThis represents the position at which the column appears in the table or view.
COLUMN_DEFAULTvarcharThe default value of the column.
IS_NULLABLEvarcharThe 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_TYPEvarcharThe 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_SIZEintegerThe size of the table or view column in bytes.
CHARACTER_MAXIMUM_LENGTHintegerThe maximum length in characters for binary data, character data, or text and image data. Otherwise, null is returned.
CHARACTER_OCTET_LENGTHintegerThe maximum length in bytes for binary data, character data, or text and image data. Otherwise, null is returned.
NUMERIC_PRECISIONintegerThe precision of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, null is returned.
NUMERIC_PRECISION_RADIXintegerThe precision radix of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, null is returned.
NUMERIC_SCALEintegerThe scale of approximate numeric data, exact numeric data, integer data, or monetary data. Otherwise, null is returned.
DATETIME_PRECISIONintegerThe supported precision for datetime and interval data types. For other data types, null is returned.
INTERVAL_TYPEintegerIf the data type is interval, then specified fields (year) are returned. Otherwise null is returned.
INTERVAL_PRECISIONintegerIf the data type is interval, then the declared precision is displayed. Otherwise null is returned.