On this page

    INFORMATION_SCHEMA.COLUMNS

    The INFORMATION_SCHEMA.COLUMNS view contains metadata for all the columns within the physical and virtual datasets in a project.

    Syntax
    SELECT * 
    FROM INFORMATION_SCHEMA.COLUMNS
    

    note:

    The list of columns for physical datasets 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 physical or virtual dataset.
    TABLE_NAMEvarcharThe name of the physical or virtual dataset that the column belongs to.
    COLUMN_NAMEvarcharThe name of the column in the physical or virtual dataset.
    ORDINAL_POSITIONintegerThis represents the position at which the column appears in the physical or virtual dataset.
    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 physical or virtual dataset. You can also view that specific dataset to see the data types for all columns.
    COLUMN_SIZEintegerThe size of the physical or virtual dataset 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.