记工作中用到的抓取oracle表结构的sql语句

时间:2021-08-18 21:53:40

以下是SQL,生成的结果中是否为主键和是否可为空,是不准确的 ,没有关联相关的系统表:

select
'' as 业务源系统,
t2.TABLE_NAME 表名称,
nvl(t3.comments,'') as 表业务含义,
t2.COLUMN_NAME 字段名称,
t2.DATA_TYPE 字段类型,
case
 when instr(t2.DATA_TYPE,'CHAR')>0 then 'string'
 when instr(t2.DATA_TYPE,'INT')>0 then 'INT'
 when instr(t2.DATA_TYPE,'NUMBER')>0 then 'decimal'
 when instr(t2.DATA_TYPE,'TIMESTAMP')>0 then 'timestamp'
 when instr(t2.DATA_TYPE,'DATE')>0 then 'timestamp'
 when instr(t2.DATA_TYPE,'FLOAT')>0 then 'decimal'
 when instr(t2.DATA_TYPE,'DOUBLE')>0 then 'decimal'
 else ''
   end
    as hive类型,
t2.DATA_LENGTH 字段长度,
'' as 是否为主键,
'' as 可为空,
t4.comments 字段含义
from all_users t1,
all_tab_columns t2,
all_tab_comments t3,
all_col_comments t4
where t1.username not in('SYS','SYSTEM','ACCESS_LOG')
and t2.OWNER=t1.username
and t3.table_name=t2.TABLE_NAME and t3.owner=t2.OWNER
and t4.table_name=t2.TABLE_NAME and t4.owner=t2.owner and t4.column_name=t2.COLUMN_NAME
order by t1.username,t2.TABLE_NAME,t2.COLUMN_ID

以上