dc-teradata.properties

LoadTablesQuery = \
select \
    trim(c.DatabaseName) as db_name, \
    trim(c.TableName) as table_name, \
    case when t.TableKind = 'G' then 1 else 0 end as gt_flag,  \
    trim(c.ColumnName) as column_name, \
    case c.ColumnType  \
        when 'A1 ' then 'ARRAY' \
        when 'AN ' then 'MULTI-DIMENSIONAL ARRAY' \
        when 'AT ' then 'TIME' \
        when 'BF ' then 'BYTE' \
        when 'BO ' then 'BLOB' \
        when 'BV ' then 'VARBYTE' \
        when 'CF ' then 'CHAR' \
        when 'CO ' then 'CLOB' \
        when 'CV ' then 'VARCHAR' \
        when 'D  ' then 'DECIMAL' \
        when 'DA ' then 'DATE' \
        when 'DH ' then 'INTERVAL DAY TO HOUR' \
        when 'DM ' then 'INTERVAL DAY TO MINUTE' \
        when 'DS ' then 'INTERVAL DAY TO SECOND' \
        when 'DY ' then 'INTERVAL DAY' \
        when 'F  ' then 'FLOAT' \
        when 'HM ' then 'INTERVAL HOUR TO MINUTE' \
        when 'HS ' then 'INTERVAL HOUR TO SECOND' \
        when 'HR ' then 'INTERVAL HOUR' \
        when 'I  ' then 'INTEGER' \
        when 'I1 ' then 'BYTEINT' \
        when 'I2 ' then 'SMALLINT' \
        when 'I8 ' then 'BIGINT' \
        when 'JN ' then 'JSON' \
        when 'MI ' then 'INTERVAL MINUTE' \
        when 'MO ' then 'INTERVAL MONTH' \
        when 'MS ' then 'INTERVAL MINUTE TO SECOND' \
        when 'N  ' then 'NUMBER' \
        when 'PD ' then 'PERIOD(DATE)' \
        when 'PM ' then 'PERIOD(TIMESTAMP WITH TIME ZONE)' \
        when 'PS ' then 'PERIOD(TIMESTAMP)' \
        when 'PT ' then 'PERIOD(TIME)' \
        when 'PZ ' then 'PERIOD(TIME WITH TIME ZONE)' \
        when 'SC ' then 'INTERVAL SECOND' \
        when 'SZ ' then 'TIMESTAMP WITH TIME ZONE' \
        when 'TS ' then 'TIMESTAMP' \
        when 'TZ ' then 'TIME WITH TIME ZONE' \
        when 'UT ' then 'UDT Type' \
        when 'XM ' then 'XML' \
        when 'YM ' then 'INTERVAL YEAR TO MONTH' \
        when 'YR ' then 'INTERVAL YEAR' \
        when '++'  then 'TD_ANYTYPE'  \
    end as data_type, \
    c.ColumnLength as "char_length", \
    c.DecimalTotalDigits as data_precision, \
    c.DecimalFractionalDigits as data_scale,  \
    CASE c.CharType  \
        WHEN 1 THEN 'LATIN' \
        WHEN 2 THEN 'UNICODE' \
        WHEN 3 THEN 'KANJISJIS' \
        WHEN 4 THEN 'GRAPHIC' \
        WHEN 5 THEN 'SET KANJI1' \
        ELSE ''  \
    END as character_set,  \
    case when c.Nullable = 'Y' then 1 else 0 end as nullable, \
    coalesce(c.DefaultValue,'') as "default_value", \
    trim(c.ColumnType) as internal_data_type, \
    upi.ColumnPosition as upi_column_num, \
    usi.ColumnPosition as usi_column_num \
from \
    DBC.ColumnsX c \
    inner join dbc.TablesX t \
        on t.DataBaseName = c.DatabaseName  \
        and t.TableName = c.TableName  \
        and t.TableKind = 'T' \
    left outer join dbc.IndicesX upi \
        on upi.DatabaseName = c.DatabaseName \
        and upi.TableName = c.TableName \
        and upi.ColumnName = c.ColumnName  \
        and upi.IndexType = 'P' \
        and upi.UniqueFlag = 'Y' \
    left outer join dbc.IndicesX usi \
        on usi.DatabaseName = c.DatabaseName \
        and usi.TableName = c.TableName \
        and usi.ColumnName = c.ColumnName  \
        and usi.IndexType = 'S' \
        and usi.UniqueFlag = 'Y' \
where c.DatabaseName not in ('console', 'Crashdumps', 'DB', 'dbcmgr', 'External_AP', 'LockLogShredder',' SQLJ', 'SysAdmin',  \
'SYSBAR', 'SYSJDBC', 'SYSLIB', 'SYSSPATIAL', 'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB', 'TD_SYSFNLIB', 'TD_SYSGPL', 'TD_SYSXML', \
'TDMaps', 'TDPUSER', 'TDQCD', 'TDStats', 'tdwm', 'viewpoint')  \
--and c.DatabaseName = 'DBT_SPRINT_D'  \
order by 1, 2, c.ColumnId