获取业务库的schema信息导出成数据字典

获取业务库的schema信息导出成数据字典

场景：需要获取业务库的schema信息导出成数据字典，以下为获取oracle与mysql数据库的schema信息语句

--获取oracle库schema信息
select
  tt1.owner as t_owner
 ,tt1.table_name
 ,tt1.column_name
 ,tt1.data_type
 ,tt1.data_length
 ,tt1.nullable
 ,tt1.comments
 ,tt1.column_id
 ,tt2.pkcols
from
(
 select
        atc.owner
       ,atc.table_name
       ,atc.column_name
       ,atc.data_type
       ,atc.data_length
       ,atc.nullable
       ,acc.comments
       ,atc.column_id
 from all_tab_columns atc
 join all_col_comments acc
 on atc.table_name=acc.table_name
    and atc.owner=acc.owner
    and atc.column_name=acc.column_name
) tt1
left join

(
 SELECT OWNER,table_name,LISTAGG(column_name,',') WITHIN group(ORDER BY column_name) AS pkcols
 FROM
 (
 select a.OWNER,a.table_name,a.column_name
 from all_cons_columns a
 join all_constraints b
 on a.constraint_name = b.constraint_name
 and a.OWNER=b.OWNER
 and b.constraint_type = 'P'
 ) t1
 GROUP BY OWNER,table_name
)tt2
on tt1.OWNER=tt2.OWNER
and tt1.table_name=tt2.table_name
order by tt1.owner,tt1.table_name, tt1.column_id;


--获取mysql库schema信息
select
  tt1.table_schema as t_owner
 ,tt1.table_name
 ,tt1.column_name
 ,tt1.data_type
 ,tt1.character_maximum_length
 ,tt1.is_nullable
 ,tt1.column_comment
 ,tt1.ordinal_position
 ,tt2.pkcols
from information_schema.COLUMNS tt1
left join
(
 select
 CONSTRAINT_SCHEMA,table_name,group_concat(column_name order by ordinal_position separator ',') as pkcols
 from information_schema.key_column_usage t
 where constraint_name='PRIMARY'
 group by CONSTRAINT_SCHEMA,table_name
)tt2
on tt1.table_schema=tt2.CONSTRAINT_SCHEMA
and tt1.table_name=tt2.table_name
order by tt1.table_schema,tt1.table_name, tt1.ordinal_position