oracle查看对象及代码

盛沈静

--查询对象 （TABLE）
select * from all_objects t
where t.object_type='TABLE'
and t.OBJECT_NAME like 'LC%'
--and owner in ('STG','DW','MDM','CTL');
and owner ='BITEST'
--查询对象 （TABLE）
select distinct owner||'.'||object_name otable,last_ddl_time
from all_objects t
where t.object_name like upper('%tb_comm_serv_fee_201012%')
and t.object_type in ('TABLE','VIEW')
and owner in ('STG','DW','MDM','CTL');

--查询列定义
select distinct owner||'.'||table_name otable
from ALL_TAB_COLUMNS@zs_ywzc t
where  t.COLUMN_NAME like '%OLD_%'
and owner in ('STG','DW','MDM','CTL');
--查询对象 （PROCEDURE）
select  distinct owner||'.'||object_name sp
from all_objects t
where t.object_name like upper('SP_JF_DISC%')
and t.object_type='PROCEDURE'
--and owner in ('ETL','STG','DW','MDM','CTL');
and owner ='BITEST'
order by sp;
--查看all_source
select distinct owner||'.'||name sp ,text
FROM ALL_SOURCE
WHERE TYPE='PROCEDURE'
and upper(text) like upper('%sp_query_jf_summary%')
and not regexp_like(upper(trim(text)),'--|/\*') --去掉注释打头的