子域名网站二级,南京it培训机构,外贸营销公司,现在济南可以正常出入吗获取业务库的schema信息导出成数据字典
场景#xff1a;需要获取业务库的schema信息导出成数据字典#xff0c;以下为获取oracle与mysql数据库的schema信息语句 --获取oracle库schema信息
selecttt1.owner as t_owner,tt1.table_name,tt1.column_name,tt1.data_type,tt1.dat…获取业务库的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_nameacc.table_nameand atc.owneracc.ownerand atc.column_nameacc.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.OWNERb.OWNERand b.constraint_type P) t1GROUP BY OWNER,table_name
)tt2
on tt1.OWNERtt2.OWNER
and tt1.table_namett2.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_namePRIMARYgroup by CONSTRAINT_SCHEMA,table_name
)tt2
on tt1.table_schematt2.CONSTRAINT_SCHEMA
and tt1.table_namett2.table_name
order by tt1.table_schema,tt1.table_name, tt1.ordinal_position
