码农之家 - 表结构
type: channel
link: http://blog.liyx.fun/index.php/tag/表结构/
items: Oracle 导出数据库结构语句

Oracle 导出数据库结构语句
type: item
date: 2021-09-13T14:28:00+08:00
description: 临时用的语句，能跑就行。SELECT T1.TABLE_NAME AS "表名", T3.COMMENTS AS "表注释", T1.COLUMN_NAME AS "列名", T2.COMMENTS AS "列注释", T1.NULLABLE AS "是否为空", T1.DATA_DEFAULT AS "默认值", T1.DATA_TYPE AS "字段类型", T1.DATA_LENGTH AS "字段长度", NVL2( T7.CONSTRAINT_TYPE, 'Y', 'N' ) AS "是否主键", NVL2( T8.CONSTRAINT_TYPE, 'Y', 'N' ) AS "是否外键" FROM ALL_TAB_COLUMNS T1 LEFT JOIN ALL_COL_COMMENTS T2 ON T1.OWNER = T2.OWNER AND T1.TABLE_NAME = T2.TABLE_NAME AND T1.COLUMN_NAME = T2.COLUMN_NAME LEFT JOIN ALL_TAB_COMMENTS T3 ON T1.OWNER = T3.OWNER AND T1.TABLE_NAME = T3.TABLE_NAME LEFT JOIN ( SELECT T5.OWNER, T5.TABLE_NAME, T5.COLUMN_NAME, T6.CONSTRAINT_TYPE FROM ALL_CONS_COLUMNS T5 INNER JOIN ALL_CONSTRAINTS T6 ON T5.OWNER = T6.OWNER AND T5.TABLE_NAME = T6.TABLE_NAME AND T5.CONSTRAINT_NAME = T6.CONSTRAINT_NAME AND T6.CONSTRAINT_TYPE = 'P' ) T7 ON T1.OWNER = T7.OWNER AND T1.TABLE_NAME = T7.TABLE_NAME AND T1.COLUMN_NAME = T7.COLUMN_NAME LEFT JOIN ( SELECT T5.OWNER, T5.TABLE_NAME, T5.COLUMN_NAME, T6.CONSTRAINT_TYPE FROM ALL_CONS_COLUMNS T5 INNER JOIN ALL_CONSTRAINTS T6 ON T5.OWNER = T6.OWNER AND T5.TABLE_NAME = T6.TABLE_NAME AND T5.CONSTRAINT_NAME = T6.CONSTRAINT_NAME AND T6.CONSTRAINT_TYPE = 'R' ) T8 ON T1.OWNER = T8.OWNER AND T1.TABLE_NAME = T8.TABLE_NAME AND T1.COLUMN_NAME = T8.COLUMN_NAME WHERE T1.OWNER = 'XXXXXXXX' --要查询的用户名 AND NOT EXISTS ( SELECT T4.OBJECT_NAME FROM ALL_OBJECTS T4 WHERE T4.OWNER = T1.OWNER AND T4.OBJECT_TYPE = 'TABLE' AND T4.TEMPORARY = 'Y' AND T4.OBJECT_NAME = T1.TABLE_NAME ) ORDER BY T1.TABLE_NAME, T1.COLUMN_ID;