Columns Schema Collection

Columns schema collection returns information about the Columns of any TABLE or VIEW object by accessing the Advanced SQL Engine Data Dictionary views. It will access DBC.COLUMNS[V]X if UseXViews is set to true, otherwise it will access DBC.COLUMNS[V].

DBC.COLUMNS and DBC.COLUMNSX return only the Column Name for VIEW objects. They will not return column metadata, such as Data Type or Precision, for VIEW objects. The .NET Data Provider for Teradata will execute additional HELP COLUMN queries to retrieve the missing metadata. In some cases the HELP COLUMN query will also fail. This results in rows with just TABLE_SCHEMA, TABLE_NAME and COLUMN_NAME data. In this case all other columns/fields are set to null or empty.

Column Name	Data Type	Description
TABLE_CATALOG	String	This field is always set to String.Empty.
TABLE_SCHEMA	String	Returns the name of the DATABASE in which the TABLE or VIEW object resides.
TABLE_NAME	String	Returns the name of the TABLE or VIEW object.
COLUMN_NAME	String	Returns the name of the COLUMN.
COLUMN_HASDEFAULT	Int32	Returns: 0 indicating the column does not have a default value. -1 indicating the column has a default value.
COLUMN_DEFAULT	String	Returns any default value assigned to the column.
IS_NULLABLE	String	Returns a code to indicate whether the column may have a null value: Y indicates it may have a null value. N indicates it may not have a null value.
COLUMN_TYPE	String	Returns the SQL Engine Data Type name. It will return the User Defined Type name if the column is a UDT. See PROVIDERDBTYPE for the .NET Data Provider for Teradata data type corresponding to the SQL Engine data type.
CHARACTER_MAXIMUM_LENGTH	Int32	Returns the maximum length of the Column in characters for the following Data Types: Char or VarChar CLOB, JSON [TEXT] XML or Dataset CSV All Interval data types Returns the maximum length of the Column in bytes for the following Data Types: Byte or VarByte BLOB, JSON [BSON or UBJSON] or Dataset Avro
CHARACTER_OCTET_LENGTH	Int32	Returns the maximum length of the Column in bytes for the following Data Types: Byte or VarByte Char or VarChar BLOB, CLOB, JSON, XML or Dataset All Interval data types
NUMERIC_PRECISION	Int16	Returns the maximum number of digits (base 10) that can be stored in a Numeric data type: ByteInt, SmallInt, Integer and BigInt Decimal or Numeric Float or Double Precision
NUMERIC_SCALE	Int16	Returns the maximum number of fractional digits (base 10) to the right of decimal point for Decimal data type.
DATETIME_PRECISION	Int16	Returns the maximum number of fractional seconds to the right of decimal point for the following data types: Timestamp and Timestamp With Time Zone Time and Time With Time Zone Interval Day To Second Interval Hour To Second Interval Minute To Second Interval Second
DESCRIPTION	String	Returns user-supplied text, or comments, associated with the Column.
LABEL	String	Returns the column heading or title.
FORMAT	String	Returns the display format of the column.
CHAR_TYPE	Int16	Returns the character set used to store data in a character type column: 1 for Latin 2 for Unicode 3 for KanjiSJIS 4 for Graphic 5 for Kanji1
PROVIDERDBTYPE	Int32	The .NET Data Provider for Teradata data type ( TdType ).
ORDINAL_POSITION	Int16	Returns the position of the column in the TABLE or VIEW.
INTERVAL_PRECISION	Int16	Returns the number of digits in the most significant field of an interval, or Null if the data type is not an interval.
TEMPORAL_TYPE	String	Returns the temporal type for a temporal column: V for a ValidTime Temporal column T for a TransactionTime Temporal column B for a Bi-Temporal column R for a Temporal Relationship column NULL for a non-Temporal column
COLUMN_INFO	String	Returns additional information about the column. If the column is defined as a NUMBER, the information that is returned indicates whether the NUMBER is a fixed or floating decimal type: FIXED for a fixed point decimal number FLOAT for a floating point decimal number If the column is defined as a pseudo period or a pseudo period beginning or ending point, this information will contain the pseudo period type concatenated to the pseudo period column field id with a colon (:). The pseudo period column field id is only populated in the pseudo period beginning or ending point types. The valid pseudo period type values are: PP Pseudo Period PB Pseudo Period Begin PE Pseudo Period End If the table is a time series table the generated columns are returned as: TIME CODE for the Time Code column TIME SEQUENCE for the Time Sequence Number column
STORAGE_FORMAT	String	The Format in which the column is stored in the SQL Engine. For JSON columns: TEXT, BSON or UBJSON For Dataset columns: AVRO or CSV
INLINE_LENGTH	Int32	Returns the Inline Length for the following data types: JSON, XML, Dataset or ST_Geometry. If the column data is shorter than this length the SQL Engine will store that data within the row itself instead of storing it in the associated LOB sub table.
DATASET_SCHEMA	String	The name of the Schema used by a Dataset column. Returns String.Empty if this is not a Dataset column, or if the Dataset contains an embedded schema.

Columns schema collection can be filtered or restricted to:

Restriction Column Name	Restriction Number	Description
TABLE_SCHEMA	1	This restriction can be set to a valid DATABASE object name.
TABLE_NAME	2	This restriction can be set to a valid TABLE object name.
COLUMN_NAME	3	This restriction can be set to a valid COLUMN name.

Columns schema collection rows are ordered by:

Column Name	Description
TABLE_SCHEMA	Ascending case sensitive sort order. For example A through Z followed by a through z.
TABLE_NAME	Ascending case sensitive sort order.
ORDINAL_POSITION	Ascending sort order. The ordinal position is dictated by the CREATE TABLE statement where the first column is assigned Ordinal 1, the second column is assigned Ordinal 2 and so forth.