oracle 得到一个表的表结构

最新推荐文章于 2022-12-17 17:45:15 发布

杭州丹尼

最新推荐文章于 2022-12-17 17:45:15 发布

阅读量280

点赞数

分类专栏： oracle 表meta信息文章标签： oracle column

本文链接：https://blog.csdn.net/dickzhu/article/details/84749741

版权

oracle 表meta信息专栏收录该内容

0 篇文章 0 订阅

订阅专栏

SELECT OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE
|| DECODE (
DATA_TYPE,
'NUMBER', DECODE (
'('
|| NVL (TO_CHAR (DATA_PRECISION), '*')
|| ','
|| NVL (TO_CHAR (DATA_SCALE), '*')
|| ')',
'(*,*)', NULL,
'(*,0)', '(38)',
'('
|| NVL (TO_CHAR (DATA_PRECISION), '*')
|| ','
|| NVL (TO_CHAR (DATA_SCALE), '*')
|| ')'),
'FLOAT', '(' || DATA_PRECISION || ')',
'DATE', NULL,
'TIMESTAMP(6)', NULL,
'(' || DATA_LENGTH || ')')
AS DATA_TYPE,
DATA_LENGTH,
NULLABLE
FROM DBA_TAB_COLUMNS
WHERE OWNER IN ('GCE_DB2')
AND TABLE_NAME = UPPER ('CCADCM')
ORDER BY OWNER, TABLE_NAME, COLUMN_ID;

SELECT TABLE_NAME||' '||COLUMN_NAME||' '||COLUMN_ID|| ' ' || DECODE(DATA_TYPE, 'CHAR' , 'VARCHAR2', 'TIMESTAMP(6)' ,'TIMESTAMP', DATA_TYPE ) ||DECODE (
DATA_TYPE,
'NUMBER', DECODE (
'('
|| NVL (TO_CHAR (DATA_PRECISION), '*')
|| ','
|| NVL (TO_CHAR (DATA_SCALE), '*')
|| ')',
'(*,*)', NULL,
'(*,0)', '(38)',
'('
|| NVL (TO_CHAR (DATA_PRECISION), '*')
|| ','
|| NVL (TO_CHAR (DATA_SCALE), '*')
|| ')'),
'FLOAT', '(' || DATA_PRECISION || ')',
'DATE', NULL,
'TIMESTAMP(6)', NULL,
'(' || DATA_LENGTH || ')') ||' '|| NULLABLE
FROM DBA_TAB_COLUMNS
WHERE OWNER IN ('GCE_DB2')
AND TABLE_NAME = UPPER ('RDMDCM')
ORDER BY OWNER, TABLE_NAME, COLUMN_ID;

SELECT TABLE_NAME||CHR(9)||COLUMN_NAME||CHR(9)||COLUMN_ID||CHR(9)|| DECODE(DATA_TYPE, 'CHAR' , 'VARCHAR2', 'TIMESTAMP(6)' ,'TIMESTAMP', DATA_TYPE ) ||DECODE (
DATA_TYPE,
'NUMBER', DECODE (
'('
|| NVL (TO_CHAR (DATA_PRECISION), '*')
|| ','
|| NVL (TO_CHAR (DATA_SCALE), '*')
|| ')',
'(*,*)', NULL,
'(*,0)', '(38)',
'('
|| NVL (TO_CHAR (DATA_PRECISION), '*')
|| ','
|| NVL (TO_CHAR (DATA_SCALE), '*')
|| ')'),
'FLOAT', '(' || DATA_PRECISION || ')',
'DATE', NULL,
'TIMESTAMP(6)', NULL,
'(' || DATA_LENGTH || ')') ||CHR(9)|| NULLABLE
FROM DBA_TAB_COLUMNS
WHERE OWNER IN ('GCE_DB2')
AND TABLE_NAME = UPPER ('RDMDCM')
ORDER BY OWNER, TABLE_NAME, COLUMN_ID;