昆明网站制作报价最近的国际新闻热点

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

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

--获取oracle库schema信息
selecttt1.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
(selectatc.owner,atc.table_name,atc.column_name,atc.data_type,atc.data_length,atc.nullable,acc.comments,atc.column_idfrom all_tab_columns atcjoin all_col_comments accon atc.table_name=acc.table_nameand atc.owner=acc.ownerand atc.column_name=acc.column_name
) tt1
left join(SELECT OWNER,table_name,LISTAGG(column_name,',') WITHIN group(ORDER BY column_name) AS pkcolsFROM(select a.OWNER,a.table_name,a.column_namefrom all_cons_columns ajoin all_constraints bon a.constraint_name = b.constraint_nameand a.OWNER=b.OWNERand b.constraint_type = 'P') t1GROUP 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信息
selecttt1.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
(selectCONSTRAINT_SCHEMA,table_name,group_concat(column_name order by ordinal_position separator ',') as pkcolsfrom information_schema.key_column_usage twhere 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