如何查询oracle一个数据库中包含有某个特定值的所有表及字段名

转：
怎么在某Schame下搜索数据:比如:在scott用户下面,搜索含有'TEST'的数据的表和字段穷举法:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

declare

v_Sql varchar2(2000);

v_count number;

begin

for xx 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 ' || xx.owner || '.' || xx.table_name ||

' where ' || xx.column_name || ' like ''%TEST%'' ';

execute immediate v_Sql

into v_count;

if (v_count >= 1) then

dbms_output.put_line(xx.table_name || ':' || xx.column_name);

end if;

exception

when others then

null;

end;

end loop;

end;