在Oracle中，是否有可能搜索每个表的每个字段以查找特定值？
有数百个表，某些表中有成千上万行数据，因此我知道查询可能需要很长时间。但我唯一知道的是我想要查询的字段的值为1/22/2008P09RR8。
我已尝试使用以下语句，根据我认为的列名查找适当的列，但没有返回结果。
SELECT * from dba_objects
WHERE object_name like '%DTN%'
这个数据库上完全没有文档记录，我也不知道这个字段是从哪里获取的。
有什么想法吗？
在Oracle中搜索所有表的所有字段以查找特定值
141
- Chris Conway
6
我们能否使用单个查询来完成这个任务，而不是使用存储过程？ - Freakyuser
是的，可以在纯SQL中完成。请参阅SQL to Search for a VALUE in all COLUMNS of all TABLES in an entire SCHEMA。 - Lalit Kumar B
@LalitKumarB 您列出的页面已不可访问。是否可能将一些信息发布为答案？ - Dodzi Dzakuma
@DodziDzakuma 该页面可访问 https://lalitkumarb.wordpress.com/2015/01/06/sql-to-search-for-a-value-in-all-columns-of-all-atbles-in-an-entire-schema/。此外，我已发布了一个答案，请向下滚动或查看 https://dev59.com/q3VC5IYBdhLWcg3wsTfv#27794127。 - Lalit Kumar B
如果您在解决Lalit Kumar的查询时遇到困难，请尝试使用此演示：http://sqlfiddle.com/#!4/76924c/2/0 - DxTx
是的，我知道已经过去了12年。Toad有一个搜索功能可以解决这个问题。 - alexherm
18个回答
115
引用：
I've tried using this statement below to find an appropriate column based on what I think it should be named but it returned no results.*
SELECT * from dba_objects WHERE object_name like '%DTN%'
列不是一个对象。如果你的意思是你希望列名像'%DTN%'，你需要的查询语句是：
SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DTN%';
但如果“DTN”字符串只是您的猜测，那可能没有帮助。
顺便问一下，您有多确定“1/22/2008P09RR8”是直接从单个列中选择的值？如果您根本不知道它来自哪里，它可能是几个列的串联，或者某个函数的结果，或者是嵌套表对象中的值。因此，您可能会在尝试检查每个列以获取该值时进行一次徒劳的追逐。您能否从显示此值的任何客户端应用程序开始，并尝试弄清楚它使用的查询是什么？
无论如何，diciu的答案提供了一种方法，可以生成SQL查询以检查每个表的每个列是否具有该值。您还可以完全在一个SQL会话中使用PL / SQL块和动态SQL执行类似的操作。以下是一些匆忙编写的代码：
SET SERVEROUTPUT ON SIZE 100000
DECLARE
match_count INTEGER;
BEGIN
FOR t IN (SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE owner <> 'SYS' and data_type LIKE '%CHAR%') LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
' WHERE '||t.column_name||' = :1'
INTO match_count
USING '1/22/2008P09RR8';
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
/
有一些方法可以使其更加高效。
在这种情况下，考虑到您要查找的值，您可以明确地排除任何 NUMBER 或 DATE 类型的列，这将减少查询的数量。甚至可以仅限于类型类似于 '%CHAR%' 的列。
您可以构建每个表的一个查询，而不是每个列的一个查询，如下所示：
SELECT * FROM table1
WHERE column1 = 'value'
OR column2 = 'value'
OR column3 = 'value'
...
;
- Dave Costa
14
1你应该将其限制在 char、varchar 和 varchar2 列上，因为数字和日期列不可能包含该字符串。 - Erich Kitzmueller
8@ammoQ -- 就像我在倒数第二段所说的那样？ - Dave Costa
我们能否使用单个查询来完成这个任务，而不是使用存储过程？ - Freakyuser
@Freakyuser - 从技术上讲，上面的内容不是存储过程，而是匿名块。但我想你更喜欢单个SQL查询。对于任何非平凡的表集，我想不到任何方法来做到这一点；由于表和列名将会有所不同，因此您需要使用动态SQL。但是，如果您将我的代码用作管道函数的基础，那么您就可以从该函数中选择作为行源，因此您将能够将结果作为查询的输出获得。 - Dave Costa
@user1286399 看起来你可能有一个混合大小写的列名。如果是这样，你需要修改动态SQL语句，在列名周围加上双引号。 - Dave Costa
显示剩余9条评论
41
我对上面的代码进行了修改，以便在只搜索一个所有者时更快地工作。您只需要更改3个变量v_owner、v_data_type和v_search_string，以适应您要搜索的内容。
SET SERVEROUTPUT ON SIZE 100000
DECLARE
match_count INTEGER;
-- Type the owner of the tables you are looking at
v_owner VARCHAR2(255) :='ENTER_USERNAME_HERE';
-- Type the data type you are look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
v_data_type VARCHAR2(255) :='VARCHAR2';
-- Type the string you are looking at
v_search_string VARCHAR2(4000) :='string to search here...';
BEGIN
FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'
INTO match_count
USING v_search_string;
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
/
- Flood
6
我必须注释掉第一行才能运行这个查询。而且我无法删除所有者过滤器并运行该查询。 - Popa Andrei
2我需要在表名/列名周围加上双引号，以避免在需要引用它们时出现问题：
'SELECT COUNT(*) FROM "'||t.table_name||'" WHERE "'||t.column_name||'" = :1' - Steve Chambers注意，all_tab_cols也包含视图，尽管名称如此。 - phil_w
1
dbms_output 究竟是什么？因为在 DataGrip 中查询成功执行，但我看不到任何结果。 - Prasannjeet Singh1我知道这有点老了，但是当我运行它时，我只能得到一个“匿名块完成”的脚本输出。 - JasonWH
截至2022年，这应该是标记的答案。:-) 谢谢，这很好用！在Oracle 10g、19c上都可以使用。 - Ak777
10
我知道这是一个老话题。但我看到有人在评论中问是否可以使用SQL而不是使用PL/SQL实现。因此想发布一个解决方案。
以下演示是为了在整个SCHEMA中搜索所有TABLES的所有COLUMNS中的VALUE：
- 搜索CHARACTER类型
让我们在SCOTT schema中查找值为KING的数据。
SQL> variable val varchar2(10)
SQL> exec :val := 'KING'
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
2 SUBSTR (table_name, 1, 14) "Table",
3 SUBSTR (column_name, 1, 14) "Column"
4 FROM cols,
5 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
6 || column_name
7 || ' from '
8 || table_name
9 || ' where upper('
10 || column_name
11 || ') like upper(''%'
12 || :val
13 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
14 ORDER BY "Table"
15 /
Searchword Table Column
----------- -------------- --------------
KING EMP ENAME
SQL>
- 搜索一个数字类型
让我们在SCOTT模式中寻找值为20的数据。
SQL> variable val NUMBER
SQL> exec :val := 20
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
2 SUBSTR (table_name, 1, 14) "Table",
3 SUBSTR (column_name, 1, 14) "Column"
4 FROM cols,
5 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
6 || column_name
7 || ' from '
8 || table_name
9 || ' where upper('
10 || column_name
11 || ') like upper(''%'
12 || :val
13 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
14 ORDER BY "Table"
15 /
Searchword Table Column
----------- -------------- --------------
20 DEPT DEPTNO
20 EMP DEPTNO
20 EMP HIREDATE
20 SALGRADE HISAL
20 SALGRADE LOSAL
SQL>
- Lalit Kumar B
3
7嗯... 使用XML似乎有些过头了。此外，出现了“在XML处理中发生错误ORA-00932：不一致的数据类型：期望NUMBER得到BLOB”。 - towi
2ORA-19202：XML处理中发生错误
ORA-00932：不一致的数据类型：期望CHAR，得到BLOB
ORA-06512：位于“SYS.DBMS_XMLGEN”的第288行
ORA-06512：位于第1行
19202. 00000 - “XML处理中发生错误%s”
*原因：处理XML函数时发生错误
*操作：检查给定的错误消息并修复相应的问题 - Mohammad Faisal
有什么想法吗？ORA-19202：XML处理中发生错误
ORA-22813：操作数值超出系统限制
ORA-06512：位于“SYS.DBMS_XMLGEN”的第288行
ORA-06512：位于第1行 - Menelaos
8
这里是另一个修改后的版本，它会比较小写子字符串匹配。这适用于Oracle 11g。
DECLARE
match_count INTEGER;
-- Type the owner of the tables you are looking at
v_owner VARCHAR2(255) :='OWNER_NAME';
-- Type the data type you are look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
v_data_type VARCHAR2(255) :='VARCHAR2';
-- Type the string you are looking at
v_search_string VARCHAR2(4000) :='%lower-search-sub-string%';
BEGIN
FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.table_name||' WHERE lower('||t.column_name||') like :1'
INTO match_count
USING v_search_string;
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
/
- xojins
8
我遇到了以下问题，针对@Lalit Kumars的回答：
解决方案如下：
ORA-19202: Error occurred in XML processing
ORA-00904: "SUCCESS": invalid identifier
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
19202. 00000 - "Error occurred in XML processing%s"
*Cause: An error occurred when processing the XML function
*Action: Check the given error message and fix the appropriate problem
解决方案如下：
WITH char_cols AS
(SELECT /*+materialize */ table_name, column_name
FROM cols
WHERE data_type IN ('CHAR', 'VARCHAR2'))
SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
SUBSTR (table_name, 1, 14) "Table",
SUBSTR (column_name, 1, 14) "Column"
FROM char_cols,
TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select "'
|| column_name
|| '" from "'
|| table_name
|| '" where upper("'
|| column_name
|| '") like upper(''%'
|| :val
|| '%'')' ).extract ('ROWSET/ROW/*') ) ) t
ORDER BY "Table"
/
- AKB
8
是的，你可以这样做，但你的数据库管理员会讨厌你并想方设法制止你，因为那样会导致大量的I/O并使数据库性能下降，因为缓存会被清除。
select column_name from all_tab_columns c, user_all_tables u where c.table_name = u.table_name;
首先，我会从运行查询开始，使用v$session和v$sqlarea。这基于Oracle版本而有所不同。这将缩小范围，不会影响所有内容。
- jim
7
我修改了Flood的脚本，使其只针对每个表执行一次，而不是每个表的每个列都执行一次，以加快执行速度。它需要Oracle 11g或更高版本。
set serveroutput on size 100000
declare
v_match_count integer;
v_counter integer;
-- The owner of the tables to search through (case-sensitive)
v_owner varchar2(255) := 'OWNER_NAME';
-- A string that is part of the data type(s) of the columns to search through (case-insensitive)
v_data_type varchar2(255) := 'CHAR';
-- The string to be searched for (case-insensitive)
v_search_string varchar2(4000) := 'FIND_ME';
-- Store the SQL to execute for each table in a CLOB to get around the 32767 byte max size for a VARCHAR2 in PL/SQL
v_sql clob := '';
begin
for cur_tables in (select owner, table_name from all_tables where owner = v_owner and table_name in
(select table_name from all_tab_columns where owner = all_tables.owner and data_type like '%' || upper(v_data_type) || '%')
order by table_name) loop
v_counter := 0;
v_sql := '';
for cur_columns in (select column_name from all_tab_columns where
owner = v_owner and table_name = cur_tables.table_name and data_type like '%' || upper(v_data_type) || '%') loop
if v_counter > 0 then
v_sql := v_sql || ' or ';
end if;
v_sql := v_sql || 'upper(' || cur_columns.column_name || ') like ''%' || upper(v_search_string) || '%''';
v_counter := v_counter + 1;
end loop;
v_sql := 'select count(*) from ' || cur_tables.table_name || ' where ' || v_sql;
execute immediate v_sql
into v_match_count;
if v_match_count > 0 then
dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records');
end if;
end loop;
exception
when others then
dbms_output.put_line('Error when executing the following: ' || dbms_lob.substr(v_sql, 32600));
end;
/
- Mike Rodey
5
如果我们知道表格和列的名称，但想要找出每个模式中字符串出现的次数：
Declare
owner VARCHAR2(1000);
tbl VARCHAR2(1000);
cnt number;
ct number;
str_sql varchar2(1000);
reason varchar2(1000);
x varchar2(1000):='%string_to_be_searched%';
cursor csr is select owner,table_name
from all_tables where table_name ='table_name';
type rec1 is record (
ct VARCHAR2(1000));
type rec is record (
owner VARCHAR2(1000):='',
table_name VARCHAR2(1000):='');
rec2 rec;
rec3 rec1;
begin
for rec2 in csr loop
--str_sql:= 'select count(*) from '||rec.owner||'.'||rec.table_name||' where CTV_REMARKS like '||chr(39)||x||chr(39);
--dbms_output.put_line(str_sql);
--execute immediate str_sql
execute immediate 'select count(*) from '||rec2.owner||'.'||rec2.table_name||' where column_name like '||chr(39)||x||chr(39)
into rec3;
if rec3.ct <> 0 then
dbms_output.put_line(rec2.owner||','||rec3.ct);
else null;
end if;
end loop;
end;
- umesh
5
我会这样做（生成您需要的所有选择）。稍后，您可以将它们提供给sqlplus：
echo "select table_name from user_tables;" | sqlplus -S user/pwd | grep -v "^--" | grep -v "TABLE_NAME" | grep "^[A-Z]" | while read sw;
do echo "desc $sw" | sqlplus -S user/pwd | grep -v "\-\-\-\-\-\-" | awk -F' ' '{print $1}' | while read nw;
do echo "select * from $sw where $nw='val'";
done;
done;
它产生：
select * from TBL1 where DESCRIPTION='val'
select * from TBL1 where ='val'
select * from TBL2 where Name='val'
select * from TBL2 where LNG_ID='val'
这段代码的作用是 - 对于user_tables中的每个table_name，获取其描述中的每个字段，并创建一个select * from table where field equals 'val'语句。
- diciu
3
搜索整个数据库的步骤：
CREATE or REPLACE PROCEDURE SEARCH_DB(SEARCH_STR IN VARCHAR2, TAB_COL_RECS OUT VARCHAR2) IS
match_count integer;
qry_str varchar2(1000);
CURSOR TAB_COL_CURSOR IS
SELECT TABLE_NAME,COLUMN_NAME,OWNER,DATA_TYPE FROM ALL_TAB_COLUMNS WHERE DATA_TYPE in ('NUMBER','VARCHAR2') AND OWNER='SCOTT';
BEGIN
FOR TAB_COL_REC IN TAB_COL_CURSOR
LOOP
qry_str := 'SELECT COUNT(*) FROM '||TAB_COL_REC.OWNER||'.'||TAB_COL_REC.TABLE_NAME||
' WHERE '||TAB_COL_REC.COLUMN_NAME;
IF TAB_COL_REC.DATA_TYPE = 'NUMBER' THEN
qry_str := qry_str||'='||SEARCH_STR;
ELSE
qry_str := qry_str||' like '||SEARCH_STR;
END IF;
--dbms_output.put_line( qry_str );
EXECUTE IMMEDIATE qry_str INTO match_count;
IF match_count > 0 THEN
dbms_output.put_line( qry_str );
--dbms_output.put_line( TAB_COL_REC.TABLE_NAME ||' '||TAB_COL_REC.COLUMN_NAME ||' '||match_count);
TAB_COL_RECS := TAB_COL_RECS||'@@'||TAB_COL_REC.TABLE_NAME||'##'||TAB_COL_REC.COLUMN_NAME;
END IF;
END LOOP;
END SEARCH_DB;
执行语句
DECLARE
SEARCH_STR VARCHAR2(200);
TAB_COL_RECS VARCHAR2(200);
BEGIN
SEARCH_STR := 10;
SEARCH_DB(
SEARCH_STR => SEARCH_STR,
TAB_COL_RECS => TAB_COL_RECS
);
DBMS_OUTPUT.PUT_LINE('TAB_COL_RECS = ' || TAB_COL_RECS);
END;
样例结果
Connecting to the database test.
SELECT COUNT(*) FROM SCOTT.EMP WHERE DEPTNO=10
SELECT COUNT(*) FROM SCOTT.DEPT WHERE DEPTNO=10
TAB_COL_RECS = @@EMP##DEPTNO@@DEPT##DEPTNO
Process exited.
Disconnecting from the database test.
- Hemanth
2
错误报告 -
ORA-00933：SQL命令未正确结束
ORA-06512：在“TBOWNER.SEARCH_DB”，第17行
ORA-06512：在“TBOWNER.SEARCH_DB”，第17行
ORA-06512：在第6行
00933. 00000 - “SQL命令未正确结束” - Tahir
当我运行这个过程时，出现了上述错误。运行如下：DECLARE
SEARCH_STR VARCHAR2(200);
TAB_COL_RECS VARCHAR2(200);
BEGIN
SEARCH_STR := 'REQ000000839496';
SEARCH_DB(
SEARCH_STR => SEARCH_STR,
TAB_COL_RECS => TAB_COL_RECS
);
DBMS_OUTPUT.PUT_LINE('TAB_COL_RECS = ' || TAB_COL_RECS);
END; - Tahir
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接