15.10 - ColumnsV[X] - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

Schema

DBC

 

View Column

Data Type

Format

Source Table.Column

DatabaseName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

TableName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

TVM.TVMName

ColumnName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

TVFields.FieldName

ColumnFormat

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

X(128)

TVFields.FieldFormat

ColumnTitle

VARCHAR(256)

UNICODE

NOT CASESPECIFIC

X(256)

TVFields.FieldTitle

SPParameterType

CHAR(1) LATIN

NOT CASESPECIFIC

NOT NULL

X(1)

TVFields.SPParameterType

ColumnType

CHAR(2) LATIN

UPPERCASE

NOT CASESPECIFIC

X(2)

TVFields.FieldType

ColumnUDTName

VARCHAR(128) UNICODE

UPPERCASE

NOT CASESPECIFIC

X(128)

TVFields.UDTName

ColumnLength

INTEGER

Z,ZZZ,ZZZ,ZZ9

TVFields.MaxLength

DefaultValue

VARCHAR(1024)

UNICODE

NOT CASESPECIFIC

X(1024) (explicit)

TVFields.DefaultValue

Nullable

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

X(1)

TVFields.Nullable

CommentString

VARCHAR(255)

UNICODE

NOT CASESPECIFIC

X(255)

TVFields.CommentString

DecimalTotalDigits

SMALLINT

-ZZ9

TVFields.TotalDigits

DecimalFractionalDigits

SMALLINT

-ZZ9

TVFields.ImpliedPoint

ColumnId

SMALLINT

NOT NULL

---,--9 (explicit)

TVFields.FieldId

UpperCaseFlag

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

X(1)

TVFields.UpperCaseFlag

Compressible

CHAR(1) LATIN

NOT CASESPECIFIC

X(1)

TVFields.Compressible

CompressValue

INTEGER

NOT CASESPECIFIC

-(10)9

TVFields.CompressValue

ColumnConstraint

VARCHAR(8192)

UNICODE

NOT CASESPECIFIC

X(255) (explicit)

TVFields.ColumnCheck

ConstraintCount

SMALLINT

NOT NULL

---,--9 (explicit)

TVFields.CheckCount

CreatorName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

CreateTimeStamp

TIMESTAMP(0)

YYYY-MM-DDBHH:MI:SS

TVFields.CreateTimeStamp

LastAlterName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

LastAlterTimeStamp

TIMESTAMP(0)

YYYY-MM-DDBHH:MI:SS

TVFields.LastAlterTimeStamp

CharType

SMALLINT

---,--9 (explicit)

TVFields.CharType

IdColType

CHAR(2) LATIN

UPPERCASE

NOT CASESPECIFIC

X(2)

TVFields.IdColType

AccessCount

BIGINT

--,---,---,---,---,---,--9

ObjectUsage.UserAccessCnt

LastAccessTimeStamp

TIMESTAMP(0)

YYYY-MM-DDBHH:MI:SS

ObjectUsage.LastAccessTime
Stamp

CompressValueList

VARCHAR(8192)

UNICODE

NOT CASESPECIFIC

X(8192)

TVFields.CompressValueList

TimeDimension

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

X(1)

TVFields.TimeDimension

VTCheckType

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

X(1)

TVFields.VTCheckType

TTCheckType

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

X(1)

TVFields.TTCheckType

ConstraintId

BYTE(4)

X(8)

AccLogRuleTbl.ConstraintId

ArrayColNumberOf
Dimensions

BYTEINT

--9

UDTInfo.ArrayColNumberOf
Dimensions

ArrayColScope

VARCHAR(3200)

LATIN

UPPERCASE

NOT CASESPECIFIC

X(45)

UDTInfo.ArrayColScope

ArrayColElementType

CHAR(2) LATIN

UPPERCASE

NOT CASESPECIFIC

X(2)

TVFields.FieldType

ArrayColElementUdtName

CHAR(30) LATIN

UPPERCASE

NOT CASESPECIFIC

X(30)

TVFields.UDTName

PartitioningColumn

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

NOT NULL

X(1)

TVFields.PartitioningColumn

ColumnPartitionNumber

BIGINT

NOT NULL

ZZZZZZZZZZZZZZZZZZ9 (explicit)

TVFields.ColumnPartition
Number

ColumnPartitionFormat

CHAR(2) LATIN

UPPERCASE

NOT CASESPECIFIC

NOT NULL

X(2)

TVFields.ColumnPartition
Format

ColumnPartitionAC

CHAR(2) LATIN

UPPERCASE

NOT CASESPECIFIC

NOT NULL

X(2)

TVFields.ColumnPartitionAC

PseudoUDTFieldId

SMALLINT

---,--9

TVFields.PseudoUDTFieldId

PseudoUDTFieldType

CHAR(2)

X(2)

TVFields.PseudoUDTFieldType

StorageFormat

VARCHAR(6)

X(6)

None

When querying DBC.Columns for a view, information on column attributes (for example, length and type) is NULL. Because column attributes correspond to the table for which they were defined, they are not stored in the Data Dictionary and are not accessible through this view. Information on the columns of views can be obtained with the HELP COLUMN statement.

Some of the column values show information related to a Teradata temporal table or an ANSI temporal table. For more information about these tables, see ANSI Temporal Table Support and Temporal Table Support.

Use the SHOW TABLE statement, HELP COLUMN statement, and the ColumnsV[X] and DBC.PartitioningConstraintsV[X] views to obtain partitioning information for a table or join index.

The HELP INDEX statement Ordered or Partitioned attribute shows if an index is partitioned. For a NoPI table, the HELP INDEX statement cannot be used to determine whether or not the table or join index is partitioned.

For information about the possible values of TimeDimension column, see Appendix A: “View Column Values.”

ArrayColElementUdtName

If the object is an ARRAY data type, the name is only recorded in the ArrayColElementUdtName column if the element type for it is a UDT.

The ArrayColElementUdt column is only included in the associated views when:

  • TVFields.FieldType = A1 or AN
  • UDTInfo.TypeId = TVFields.TableId
  • ColumnUDTName

    If the object is an ARRAY data type, the name is only recorded in the ColumnUDTName column if the element type for it is a UDT.

    ColumnPartitionNumber

    This column is set to the column partition number of the column partition in which the column belongs. 0 indicates the column is not partitioned.

    Note: Columns of a table or join index with the same column partition number belong to the same column partition.

    CompressValueList

    The column contains the name of the compression routine where any complex data type column is defined to use the complex data type internal compression (for example, for a JSON data type, the CompressValueList column value would be “JSON_COMPRESS”).

    DecimalTotalDigits

    A value of -128 for the DecimalTotalDigits column indicates that the default is used.

    DecimalFractionalDigits

    The DecimalFractionalDigits column is always -128.

    Possible Values for ArrayColElementType

     

    Value

    Description

    A1

    One dimensional ARRAY data type

    AN

    Multidimensional ARRAY data type

    The ArrayColElementType column is only included in the associated views when:

  • TVFields.FieldType = A1 or AN
  • UDTInfo.TypeId = TVFields.TableId
  • Possible Values for ArrayColNumberOfDimensions

    The range of possible values is 1 through 5.

    The ArrayColNumberOfDimensions column is only included in the view when all of the following are true:

  • TVFields.FieldType = A1 or AN
  • UDTInfo.TypeId = TVFields.TableId
  • For more information about ARRAY data type dimensions, see SQL Data Definition Language and SQL Data Types and Literals.

    Possible Values for ArrayColScope

    The ArrayColScope column is only populated when the data type is created. The bound values for each dimension are a string in the [n:m] format.

    The ArrayColScope column is only included in the view when all of the following are true:

  • TVFields.FieldType = A1 or AN
  • UDTInfo.TypeId = TVFields.TableId
  • Possible Values for Compressible

     

    Value

    Description

    A

    Algorithmic compression (ALC) column

    C

    Multi-value compression column

    N

    Non-compress columns

    U

    Any complex data type column defined to use the Teradata internal compression scheme (for example, the JSON data type). Complex data types are provided by Teradata. They are similar in functionality to user-defined types because they follow an object-oriented model.

    Possible Values for CharType

     

    Value

    Description

    1

    Latin

    2

    Unicode

    3

    KanjiSJIS

    4

    Graphic

    5

    Kanji1

    0

    All other data types

    To get information about the storage format of a JSON type column see “Possible Values for StorageFormat” on page 97.

    Possible Values for ColumnPartitionAC

     

    Value

    Description

    NA

    Not column partitioned

    NC

    No auto compress

    AC

    Auto compress

    Possible Values for ColumnPartitionFormat

     

    Value

    Description

    NA

    Not column partitioned

    CS

    System-determined COLUMN format

    CU

    User-specified COLUMN format

    RS

    System-determined ROW format

    RU

    User-determined ROW format

    Possible Values for ColumnType

    Note: UF, UV, LF, and LV are internally generated column types for Data Dictionary (database DBC) data only. For character user data, the field type is set to CF or CV.

     

    Value

    Description

    ++

    TD_ANYTYPE

    A1

    One dimensional ARRAY data type

    AT

    ANSI Time

    BF

    BYTE Fixed

    BO

    Byte Large Object

    BV

    Byte Varying

    CF

    Character Fixed

    CO

    Character Large Object

    CV

    Character Varying Latin

    D

    Decimal

    DA

    Date

    DH

    Interval Day To Hour

    DM

    Interval Day To Minute

    DS

    Interval Day To Second

    DY

    Interval Day

    F

    Float

    HM

    Interval Hour To Minute

    HR

    Interval Hour

    HS

    Interval Hour To Second

    I1

    1 Byte Integer

    I2

    2 Byte Integer

    I8

    8 Byte Integer

    I

    4 Byte integer

    LF

    Pre-TD12.0 Character Fixed Locale (Kanji1 or Latin)

    Note: This column type is internally generated for Data Dictionary (database DBC) data only.

    LV

    Pre-TD12.0 Character Varying Locale (Kanji1 or Latin)

    MI

    Interval Minute

    MO

    Interval Month

    MS

    Interval Minute To Second

    N

    Number

    PD

    PERIOD(DATE)

    PM

    PERIOD(TIMESTAMP(n) WITH TIMEZONE)

    PS

    PERIOD(TIMESTAMP (n))

    PT

    PERIOD(TIME(n))

    PZ

    PERIOD (TIME(n) WITH TIME ZONE)

    SC

    Interval Second

    SZ

    Timestamp With Time Zone

    TS

    Timestamp

    TZ

    ANSI Time With Time Zone

    UF

    Character Fixed Unicode

    Note: This column type is internally generated for Data Dictionary (database DBC) data only.

    UT

    UDT Type

    UV

    Character Varying Unicode

    Note: This column type is internally generated for Data Dictionary (database DBC) data only.

    YI

    Year Interval

    YM

    Interval Year To Month

    YR

    Year

    Possible Values for IdColType

     

    Value

    Description

    NULL

    Non-identity column

    GA

    Generated always

    GE

    Generated always as row end. The required CREATE/ALTER TABLE (ANSI system-time table form) column attribute that defines the ending bound of a system-time period.

    For more information about the CREATE/ALTER TABLE (ANSI system-time table form) statement, see ANSI Temporal Table Support.

    GD

    Generated by default

    GS

    Generated always as row start. The required CREATE/ALTER TABLE (ANSI system-time table form) column attribute that defines the beginning bound of a system-time period.

    For more information about the CREATE/ALTER TABLE (ANSI system-time table form) statement, see ANSI Temporal Table Support.

    Possible Values for PartitioningColumn

     

    Value

    Description

    N

    Not a partitioning column

    Y

    Partitioning column of a partitioning expression

    Possible Values for PseudoUDTFieldType

     

    Value

    Description

    PB

    Start column of the derived period column

    PE

    End column of the derived period column

    PP

    Derived period column

    NULL

    The column is not a start or end column of a derived period column or is a derived period column.

    Possible Values for SPParameterType

     

    Value

    Description

    B

    INOUT parameter (for stored procedure only)

    C

    Column for TABLE function

    E

    Result type of the external function or method

    I

    Input parameter of a function or method

    O

    OUT parameter of a function or method

    S

    SELF parameter

    Possible Values for StorageFormat

    StorageFormat specifies the storage format of any column that has multiple storage formats (such as a JSON column), or NULL if the column does not have this characteristic.

     

    Value

    Description

    NULL

    A column that does not have multiple storage formats.

    TEXT

    Column contains JSON content in JSON CHARACTER SET LATIN or JSON CHARACTER SET UNICODE.

    BSON

    Column contains JSON content in Binary JSON format.

    UBJSON

    Column contains JSON content in Universal Binary JSON format.

    There are also several commands that provide storage format information: HELP TABLE, HELP COLUMN, HELP CAST, HELP TYPE, and SHOW TABLE. HELP TABLE provides information about the character set of the JSON type. HELP COLUMN returns information about the storage format of the JSON type; for example, NULL (for a column that does not have multiple storage formats), JSON stored as LATIN or UNICODE text, Binary JSON, or Universal Binary JSON format. Note, when running these commands the data type format is always JN for a JSON column, regardless of the storage format. For more information see SQL Data Definition Language Syntax and Examples.

    Possible Values for TTCheckType

     

    Value

    Description

    A

    ANSIQUALIFIER

    Note: The TTCheckType column returns the value A when the qualifier is ANSIQUALIFIER for column-level CHECK constraints.

    NULL

    No transaction-time dimension

    C

    CURRENT TRANSACTIONTIME

    Possible Values for UpperCaseFlag

    Note: Case flags U, C, and B are valid only for CHAR, VARCHAR, and LONG VARCHAR columns.

     

    Value

    Description

    U

    Uppercase, not specific

    C

    Not uppercase, specific

    N

    Not uppercase, not specific

    B

    Both

    Possible Values for VTCheckType

     

    Value

    Description

    A

    ANSIQUALIFIER.

    Note: The VTCheckType column returns the value A when the qualifier is ANSIQUALIFIER for column-level CHECK constraints.

    NULL

    No valid-time dimension

    C

    CURRENT VALIDTIME

    S

    SEQUENCED VALIDTIME

    N

    NONSEQUENCED VALIDTIME

    PseudoUDTFieldId

    This column is the field ID of the derived period column to which the start or end column belongs.

    The PseudoUDTFieldId column is NULL if it is not a start or end column of a derived period column or is a derived period column.

    Example  

    This example shows a statement that selects from DBC.Columns the name, format, null status, and data type of all columns in the Personnel.Employee table:

    ==> SELECT ColumnName,ColumnFormat,Nullable,ColumnType
           FROM DBC.ColumnsV WHERE DatabaseName=’Personnel’
           AND TableName = ’Employee’;

    Partial Results:

    ColumnName     ­ColumnFormat    ­Nullable   ­ColumnType
    ----------     ------------    --------   -------------
    EmpNo          9(5)            N          I
    Name           X(12)           N          CV
    DeptNo         999             Y          I
    JobTitle       X(12)           Y          CV
    Salary         zzz,zz9.99      Y          D
    YrsExp         z9              Y          I

    Example  

    This example shows a statement that selects any available commentary about columns in the Employee table:

    ==> SELECT ColumnName,CommentString FROM DBC.ColumnsV
           WHERE DatabaseName=’Personnel’ AND 
           TableName=’Employee’
           ORDER BY Columnid;

    Result:

    ColumnName      CommentString
    -----------     ---------------------------------------    
    EmpNo           Employee serial number.
    Name            Employee name, last then first initial.
    DeptNo
    JobTitle
    Salary
    YrsExp

    For more information about partitioning columns, see SQL Data Definition Language.