--用户下,所有表的所有字段的合集,可以用于查询线上线下数据库用户间的差别
select 'select '
||listagg(lower(t.column_name), ',') WITHIN GROUP(ORDER BY t.column_name)
||' from '
||t.table_name
||';--'
||s.comments
from user_tab_columns t
left join USER_TAB_COMMENTS s on t.table_name=s.table_name
where t.table_name not like 'QRTZ_%' and t.table_name not like 'OAUTH_%' and t.table_name not like 'QUARTZ_%'
--and t.table_name in upper('表名')
--不想查询的字段名
--and t.column_name not in ('字段名','字段名')
group by t.table_name,s.comments
order by t.table_name
结果案例:
select cjr,cjsj,dm,ip,kyf,mac,mc,mc_en,sxh,zhxgr,zhxgsj from T_DM_NDB;--T_代码_年度表
select cjr,cjsj,ip,kyf,mac,njdm,njmc,njmc_en,sxh,zhsjzt,zhxgr,zhxgsj from T_DM_NJB;--T_代码_年级表
select cjr,cjsj,dm,ip,kyf,mac,mc,mc_en,sxh,zhsjzt,zhxgr,zhxgsj from T_DM_PYFSB;--T_代码_培养方式表
select cjr,cjsj,dm,ip,kyf,mac,mc,mc_en,sxh,zhsjzt,zhxgr,zhxgsj from T_DM_SKYYB;--T_代码_授课语言表