Oracle导出表结构

Oracle导出表结构。

表结构Sql

-- 表结构导出
SELECT
B.TABLE_NAME AS "表名",
C.COMMENTS AS "表说明",
B.COLUMN_NAME AS "属性",
A.COMMENTS AS "描述",
case when B.DATA_TYPE ='VARCHAR2' then  B.DATA_TYPE ||'('|| B.DATA_LENGTH || ')'
when B.DATA_TYPE ='DATE' then B.DATA_TYPE 
when B.DATA_TYPE = 'NUMBER' and B.DATA_SCALE != 0  then B.DATA_TYPE ||'('|| B.DATA_PRECISION || ',' || B.DATA_SCALE || ')'
when B.DATA_TYPE = 'NUMBER' then B.DATA_TYPE ||'('|| B.DATA_PRECISION || ')'
end AS "DB数据类型",
case when B.DATA_TYPE ='VARCHAR2' then  'String'
when B.DATA_TYPE ='DATE' then 'Date'
when B.DATA_TYPE = 'NUMBER' and B.DATA_SCALE != 0 then 'Double'
when B.DATA_TYPE = 'NUMBER' then 'Integer'
end AS "类型",
'' as 规则,
B.DATA_DEFAULT as 默认值,
case when B.NULLABLE = 'N' then 'not null'
    when B.NULLABLE = 'Y' then '' end
  as 必填,
'' as 备注
FROM
ALL_COL_COMMENTS A,
ALL_TAB_COLUMNS B,
ALL_TAB_COMMENTS C
WHERE
A.TABLE_NAME IN (SELECT U.TABLE_NAME FROM USER_ALL_TABLES U)
AND A.OWNER = B.OWNER
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND C.TABLE_NAME = A.TABLE_NAME
AND C.OWNER = A.OWNER
AND A.OWNER = 'XXX' --用户名(区分大小写)
AND A.TABLE_NAME = 'XXX' --表名(区分大小写)
ORDER BY A.TABLE_NAME, B.COLUMN_ID;

主键、索引、分区

-- 1.查看索引字段
select t.TABLE_NAME,t.COLUMN_NAME from user_ind_columns t,user_indexes i where t.index_name = i.index_name and
t.table_name='XXX' -- 替换自己使用的表名即可
GROUP by t.TABLE_NAME,t.COLUMN_NAME;
-- 2. 查看主键字段
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and
au.constraint_type = 'P' AND cu.table_name = 'XXX'; -- 替换自己使用的表名即可
-- 3. 查看分区字段
SELECT * FROM all_PART_KEY_COLUMNS where OWNER = 'ZC' and name ='XXX'; -- 替换自己使用的表名即可

表结构示例