根据 值 查找 表名 字段名

2015/03/02 34222

清华大佬耗费三个月吐血整理的几百G的资源，免费分享！....>>>

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE
    var_sql VARCHAR2(1000);
    var_value VARCHAR2(100):='要查的值';
    var_cnt number;
    var_table_owner varchar2(20):='表的所有者';
BEGIN
    
    For table_detail In (select table_name,column_name from all_tab_columns where owner=var_table_owner) Loop
        Begin
           var_sql := 'select count(*) from '||var_table_owner||'.' ||table_detail.table_name||' where trim('||table_detail.column_name||')='''||var_value||'''';
        execute immediate var_sql into var_cnt;
        exception when others then
                   var_cnt:=0;
      End; 
      if(var_cnt>0)then
         dbms_output.put_line('Table Name:'||table_detail.table_name||'-->Column Name:'||table_detail.column_name);
      end if;
         
    End Loop;
END;