teradata查询表结构

最新推荐文章于 2022-01-22 21:20:10 发布

w396657279

最新推荐文章于 2022-01-22 21:20:10 发布

阅读量5.7k

点赞数 1

分类专栏： Teradata 文章标签：数据库 shell

本文链接：https://blog.csdn.net/w396657279/article/details/83974298

版权

Teradata 专栏收录该内容

23 篇文章 1 订阅

订阅专栏

show table tablename;

help table tablename;

SELECT  UPPER(TRIM(T1.COLUMNNAME)), 
            COALESCE(T1.COLUMNTITLE,'NO TITLE'), 
            CASE WHEN T1.COLUMNTYPE = 'BF' THEN
				       	'BYTE'
				       	WHEN T1.COLUMNTYPE = 'I1' THEN
				       	'BYTEINT'
				       	WHEN T1.COLUMNTYPE = 'VF' THEN
				       	'VARBYTE'
				       	WHEN T1.COLUMNTYPE = 'DA' THEN
				       	'DATE'
				       	WHEN T1.COLUMNTYPE = 'F' THEN
				       	'FLOAT'
				       	WHEN T1.COLUMNTYPE = 'I2' THEN
				       	'SMALLINT'
				       	WHEN T1.COLUMNTYPE = 'I' THEN
				       	'INTEGER'
				       	WHEN T1.COLUMNTYPE = 'CF' THEN
				       	'CHAR('|| TRIM(T1.COLUMNLENGTH) ||')'
				       	WHEN T1.COLUMNTYPE = 'CV' THEN
				       	'VARCHAR('|| TRIM(T1.COLUMNLENGTH) ||')'
				       	WHEN T1.COLUMNTYPE = 'D' THEN
				       	'DECIMAL' || '(' || T1.DECIMALTOTALDIGITS || ',' || T1.DECIMALFRACTIONALDIGITS || ')'
				  ELSE  
				       	 'UNKNOW'
				  END, 
          T1.NULLABLE,
          CASE WHEN T2.COLUMNNAME is NULL THEN 'N' ELSE 'Y' END  
    FROM DBC.COLUMNS T1
    LEFT JOIN DBC.Indices T2
    ON T1.DATABASENAME = T2.DATABASENAME
    AND T1.TABLENAME = T2.TABLENAME
    AND T1.COLUMNNAME = T2.COLUMNNAME
    AND (T2.IndexType = 'Q' or T2.IndexType = 'P')
    WHERE T1.DATABASENAME='p_pdata'
    AND   T1.TABLENAME='ods_prpcmain'
    order by T1.ColumnId;