Oracle如何查找指定字符串所出现的表
declare v_sql varchar2(2000); v_count number; begin for cur in(select t.owner, t.table_name, t.column_name from dba_tab_columns t where t.owner = 'SCOTT') loop begin v_sql := 'select count(1) from ' || cur.owner || '.' || cur.table_name || ' where ' || cur.column_name || ' like ''%CLARK%'''; execute immediate v_sql into v_count; if(v_count >= 1) then dbms_output.put_line(cur.owner || ':' || cur.table_name || ':' || cur.column_name); end if; exception when others then null; end; end loop; end; /
可是你忘记了'CLARK'这个字符在哪张表里了,如何找出来?
说明:
# 如果你不确定表是哪个用户的,就把where t.owner = 'SCOTT'去掉进行全库搜索,但是效率会很低
# 在plsqldeveloper command window中执行上面代码
结果就是CLARK出现在scott用户下的emp表的ename列中
凡哥,别他妈吹牛逼了