How to copy column names from Teradata

Question

I want to export the column names into excel sheet by running the query in Teradata. I used ctrl+c but it didnt work. Thanks in advance.

Answer 1

Change your settings in Result Set View Preferences\Copy Options\ check "Copy Include Column Headers"

The "Result Set View Preferences" is the first button that looks like pliers on the result set view window.

Answer 2

To get column names, open you answerset, and save the result set. File > save as >

Answer 3

I've found the answer show table tablename

Answer 4

I frequently want to report out the colums and the dbc.columns is the best way to do this. They all come out right padded so a trim makes them paste into excel nicely. I also added a case statement that will translate the datatype for you.

sel
  trim(databasename)
, trim(tablename)
, trim(columnname)
, max(case
   when columntype = 'D'  then 'decimal(' || decimaltotaldigits || ', ' || decimalfractionaldigits || ')'
   when columntype = 'CV' then 'varchar(' || columnlength || ')'
   when columntype = 'CF' then 'char('    || columnlength || ')'
   when columntype like 'I%'  then 'integer'
   else 'unknown' end) as colDDL
from dbc.columns where tablename in (<sometableList>)
and databasename in (<someDBList>)
order by column_id

A Full List of DBC.columns data tpe mapping example:

create volatile table vt_woe_col_list
as (
select
  trim(columnname) as column_name
, case when ColumnType in ('CF','CV')             then 'Character'
       when ColumnType in ('D','F','I1','I2','I') then 'Numeric'
       when ColumnType in ('DA')                  then 'Date'
       when ColumnType in ('SZ','TS')             then 'TimeStamp'
       else 'Skip' end as process_type
, case trim(columntype)
    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 'GF' then 'GRAPHIC'
    when 'GV' then 'VARGRAPHIC'
    when 'HM' then 'INTERVAL HOUR TO MINUTE'
    when 'HR' then 'INTERVAL HOUR'
    when 'HS' then 'INTERVAL HOUR TO SECOND'
    when 'I1' then 'BYTEINT'
    when 'I2' then 'SMALLINT'
    when 'I'  then 'INTEGER'
    when 'MI' then 'INTERVAL MINUTE'
    when 'MO' then 'INTERVAL MONTH'
    when 'MS' then 'INTERVAL MINUTE TO SECOND'
    when 'SC' then 'INTERVAL SECOND'
    when 'SZ' then 'TIMESTAMP WITH TIME ZONE'
    when 'TS' then 'TIMESTAMP'
    when 'TZ' then 'TIME WITH TIME ZONE'
    when 'YM' then 'INTERVAL YEARTO MONTH'
    when 'YR' then 'INTERVAL YEAR'
    when 'UT' then 'UDT Type'
    end as column_type_desc
,  a.*
from dbc.columns A
where trim(tablename   )='t_woe_data_samp'
  and trim(databasename)= 'DUCSMAD'
) with data
primary index(column_name)
on commit preserve rows;