如何在没有访问表的情况下查看Teradata中视图的“表结构”？

Question

有没有类似的命令可以使用：

SHOW VIEW DATABASE_NAME.VIEW_NAME;

这会显示实际的模式（columnName，columnType），而不是用于构建该视图的创建语句吗？

注：我无法查看表本身，只能查看视图。

- boethius

---

是的，HELP COLUMN DATABASE_NAME.VIEW_NAME.*。 - dnoeth

@dnoeth - 是的，不过有一个缺点 - 列类型显示为代码（例如 DA 表示 date，CF 表示 char）。 - David דודו Markovitz

@dnoeth 谢谢！这就是我要走的方向，但完整的“创建脚本”使自动化变得更容易 :) - boethius

Answer 1

1.

Teradata SQL助手 - 列出列

2.

基于视图结果创建一个表，然后使用show table。

---

create table my_table as 
(select * from some_view.some_table)
with no data
no primary index
;

show table my_table
;

3.

在选项2之后：

select          case when row_number () over (order by c.ColumnId) = 1 then ' ' else ',' end  

            ||  trim (c.ColumnName) 
            ||  ' '

            ||  case    c.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 '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 'I8' then 'bigint'
                    when 'I ' then 'int'
                    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 'PS' then 'period(timestamp('
                    when 'PT' then 'period(time('
                    when 'SC' then 'interval second'
                    when 'SZ' then 'timestamp with time zone'
                    when 'TS' then 'timestamp'
                    when 'TZ' then 'time with time zone'
                    when 'YI' then 'interval year'
                    when 'YM' then 'interval year to month'                         

                end

            ||  case when c.columntype in ('BF','BV') then '(' || cast (cast (c.ColumnLength            as format '-(9)9') as varchar (10)) || ')'  else '' end
            ||  case when c.columntype in ('CF','CV') then '(' || cast (cast (c.ColumnLength            as format '-(9)9') as varchar (10)) || ') character set ' || case c.CharType when 1 then 'latin' when 2 then 'unicode' end   else '' end           
            ||  case when c.columntype in ('AT','TS') then '(' || cast (cast (c.DecimalFractionalDigits as format '9'    ) as varchar (1))  || ')'  else '' end
            ||  case when c.columntype in ('PS','PT') then '(' || cast (cast (c.DecimalFractionalDigits as format '9'    ) as varchar (1))  || '))' else '' end
            ||  case when c.columntype in ('D'      ) then '(' || cast (cast (c.DecimalTotalDigits      as format '-(9)9') as varchar (10)) || ',' || cast (cast (c.DecimalFractionalDigits   as format '9') as varchar (1)) || ')' else '' end           

            as columns_definitions  


from        dbc.columnsV c

where       c.databasename  =   'my_database'
        and c.tablename     =   'my_table'

order by    c.ColumnId   
;

- David דודו Markovitz

---

这个答案很棒 - 我可以轻松地编写脚本，在拉取之前复制所有模式，谢谢！ - boethius

不客气。附言：请检查更新后的答案（选项 3）。 - David דודו Markovitz

嗨 Dudu，易失性表不在 dbc 表中列出 :) - dnoeth

@dnoeth - 该死。更新了答案 :-) 它是否存储在可访问的地方？ - David דודו Markovitz