sql - 在没有访问表的情况下查看 Teradata 中的 View 的 "Table Schema"？

Question

是否有类似的命令:

SHOW VIEW DATABASE_NAME.VIEW_NAME;

这显示了实际架构(columnName、columnType)而不是用于构建该 View 的创建语句？

注意我无法查看表格本身，只能查看 View 。

Answer 1

1.

Teradata SQL 助手 - 列出列

2.

根据 View 结果创建一个表，然后使用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   
;