oracle 查询所有表结构,整理当前数据库所有表结构

存在的问题: 1.定义为NUMBER未设置长度,会显示为 NUMBER(,)

                    2.默认值为long 不能显示字段的信息(navicat中可以显示)

效果图:默认值暂不能正常显示, 我试着long转char无果,大佬若是能解决麻烦评论一下

--查询当前数据库所有表结构
SELECT c.TABLE_NAME 表名,
       tc.COMMENTS 表备注,
       c.COLUMN_ID 序号,
       c.COLUMN_NAME 字段名,
       (case
         when DATA_TYPE = 'NUMBER' then
          DATA_TYPE || '(' || to_char(DATA_PRECISION) || ',' ||
          to_char(DATA_SCALE) || ')'
         when DATA_TYPE = 'DATE' then
          DATA_TYPE
         else
          DATA_TYPE || '(' || DATA_LENGTH || ')'
       end) as 数据类型,
       NullAble "On null",
       (case (select cons.CONSTRAINT_TYPE
            from user_cons_columns conc
            join (select *
                   from user_constraints
                  where CONSTRAINT_TYPE = 'P'
                     or CONSTRAINT_TYPE = 'R') cons
              on conc.CONSTRAINT_NAME = cons.CONSTRAINT_NAME
           where conc.TABLE_NAME = c.TABLE_NAME
             and conc.COLUMN_NAME = c.COLUMN_NAME)
         when 'P' then
          '主键'
         when 'R' then
          '外键'
       end) as 主外键,
       DATA_DEFAULT 默认值,
       cc.COMMENTS 备注
  FROM USER_TAB_COLUMNS c
  left join USER_COL_COMMENTS cc
    on c.TABLE_NAME = cc.TABLE_NAME
   and c.COLUMN_NAME = cc.COLUMN_NAME
  left join USER_TAB_COMMENTS tc
    on c.TABLE_NAME = tc.TABLE_NAME
 where c.TABLE_NAME in (select TABLE_NAME from user_tables)
 order by c.TABLE_NAME, c.COLUMN_ID

--查询所有数据库
select distinct owner from ALL_TABLES order by owner
--查询某数据库有多少表,不包含视图
select t.TABLE_NAME from ALL_TABLES t WHERE t.owner ='库名'
----主外键 约束类型:P 主键 R 外键 U 唯一 C 检查
select b.OWNER,b.table_name, b.column_name,c.constraint_type
  from user_cons_columns b
  join user_constraints c
    on c.CONSTRAINT_NAME = b.CONSTRAINT_NAME
--where c.constraint_type like '%约束类型%'
--and b.table_name like '%表名%'
=========================================================================================
--查询所有表结构, OWNER为数据库名,主键必须设置约束且所属当前"登录"数据库才能识别,原因:user_constraints
SELECT
--OWNER 数据库,
 c.TABLE_NAME 表名,
 tc.COMMENTS 表备注,
 c.COLUMN_ID 序号,
 c.COLUMN_NAME 字段名,
 (case
   when DATA_TYPE = 'NUMBER' then
    DATA_TYPE || '(' || to_char(DATA_PRECISION) || ',' ||
    to_char(DATA_SCALE) || ')'
   when DATA_TYPE = 'DATE' then
    DATA_TYPE
   else
    DATA_TYPE || '(' || DATA_LENGTH || ')'
 end) as 数据类型,
 NullAble "On null",
 (case (select cons.CONSTRAINT_TYPE
      from user_cons_columns conc
      join (select *
             from user_constraints
            where CONSTRAINT_TYPE = 'P'
               or CONSTRAINT_TYPE = 'R') cons
        on conc.CONSTRAINT_NAME = cons.CONSTRAINT_NAME
     where conc.TABLE_NAME = c.TABLE_NAME
       and conc.COLUMN_NAME = c.COLUMN_NAME)
   when 'P' then
    '主键'
   when 'R' then
    '外键'
 end) as 主外键,
 DATA_DEFAULT 默认值,
 cc.COMMENTS 备注
  FROM ALL_TAB_COLUMNS c
  left join ALL_COL_COMMENTS cc
    on c.TABLE_NAME = cc.TABLE_NAME
   and c.COLUMN_NAME = cc.COLUMN_NAME
   and c.owner = cc.OWNER
  left join ALL_TAB_COMMENTS tc
    on c.OWNER = tc.OWNER
   and c.TABLE_NAME = tc.TABLE_NAME
 where c.owner = '库名'
   and c.TABLE_NAME in
       (select TABLE_NAME from ALL_TABLES WHERE owner = '库名')
--and c.COLUMN_ID = '1' --第一个字段,可以统计表格个数
--and c.TABLE_NAME = 'RBS_T_APPL_AGENCY' --某表
 order by c.TABLE_NAME, c.COLUMN_ID