SQL 在所有表中的所有字段中搜索特定值 (Oracle)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/208493/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Search All Fields In All Tables For A Specific Value (Oracle)
提问by Chris Conway
Is it possible to search every field of every table for a particular value in Oracle?
是否可以在 Oracle 中搜索每个表的每个字段以查找特定值？
There are hundreds of tables with thousands of rows in some tables so I know this could take a very long time to query. But the only thing I know is that a value for the field I would like to query against is 1/22/2008P09RR8.
<
有些表中有数百个表和数千行，所以我知道这可能需要很长时间来查询。但我唯一知道的是，我要查询的字段的值是1/22/2008P09RR8. <
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%'
There is absolutely no documentation on this database and I have no idea where this field is being pulled from.
绝对没有关于这个数据库的文档，我不知道这个字段是从哪里提取的。
Any thoughts?
有什么想法吗？
采纳答案by Dave Costa
Quote:
引用：
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%'
我已经尝试使用下面的这个语句根据我认为它应该命名的内容找到一个合适的列，但它没有返回任何结果。*
SELECT * from dba_objects WHERE object_name like '%DTN%'
A column isn't an object. If you mean that you expect the column name to be like '%DTN%', the query you want is:
列不是对象。如果您的意思是希望列名类似于 '%DTN%'，那么您想要的查询是：
SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DTN%';
But if the 'DTN' string is just a guess on your part, that probably won't help.
但是，如果“DTN”字符串只是您的猜测，那可能无济于事。
By the way, how certain are you that '1/22/2008P09RR8' is a value selected directly from a single column? If you don't know at all where it is coming from, it could be a concatenation of several columns, or the result of some function, or a value sitting in a nested table object. So you might be on a wild goose chase trying to check every column for that value. Can you not start with whatever client application is displaying this value and try to figure out what query it is using to obtain it?
顺便说一下，您有多确定“1/22/2008P09RR8”是直接从单列中选择的值？如果您根本不知道它来自哪里，它可能是几个列的串联，或者某个函数的结果，或者嵌套表对象中的一个值。因此，您可能会想办法检查每一列是否有该值。您不能从显示此值的任何客户端应用程序开始并尝试找出它使用什么查询来获取它吗？
Anyway, diciu's answer gives one method of generating SQL queries to check every column of every table for the value. You can also do similar stuff entirely in one SQL session using a PL/SQL block and dynamic SQL. Here's some hastily-written code for that:
无论如何，diciu 的回答提供了一种生成 SQL 查询以检查每个表的每一列的值的方法。您还可以使用 PL/SQL 块和动态 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;
/
There are some ways you could make it more efficient too.
还有一些方法可以让它更有效率。
In this case, given the value you are looking for, you can clearly eliminate any column that is of NUMBER or DATE type, which would reduce the number of queries. Maybe even restrict it to columns where type is like '%CHAR%'.
在这种情况下，给定您要查找的值，您可以清楚地消除任何 NUMBER 或 DATE 类型的列，这将减少查询次数。甚至可能将其限制为类型类似于“%CHAR%”的列。
Instead of one query per column, you could build one query per table like this:
您可以像这样为每个表构建一个查询，而不是每列一个查询：
SELECT * FROM table1
WHERE column1 = 'value'
OR column2 = 'value'
OR column3 = 'value'
...
;
回答by Flood
I did some modification to the above code to make it work faster if you are searching in only one owner. You just have to change the 3 variables v_owner, v_data_type and v_search_string to fit what you are searching for.
如果您只搜索一位所有者，我对上述代码进行了一些修改，以使其运行速度更快。您只需更改 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;
/
回答by xojins
Here is another modified version that will compare a lower substring match. This works in Oracle 11g.
这是另一个修改后的版本，它将比较较低的子字符串匹配。这适用于 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;
/
回答by jim
Yes you can and your DBA will hate you and will find you to nail your shoes to the floor because that will cause lots of I/O and bring the database performance really down as the cache purges.
是的，您可以，您的 DBA 会讨厌您，并且会发现您将鞋子钉在地板上，因为这会导致大量 I/O 并在缓存清除时真正降低数据库性能。
select column_name from all_tab_columns c, user_all_tables u where c.table_name = u.table_name;
for a start.
作为一个开始。
I would start with the running queries, using the v$sessionand the v$sqlarea. This changes based on oracle version. This will narrow down the space and not hit everything.
我将从正在运行的查询开始，使用v$session和v$sqlarea。这会根据 oracle 版本进行更改。这将缩小空间而不是击中一切。
回答by Lalit Kumar B
I know this is an old topic. But I see a comment to the question asking if it could be done in SQLrather than using PL/SQL. So thought to post a solution.
我知道这是一个古老的话题。但是我看到对这个问题的评论，询问是否可以在SQL而不是使用PL/SQL. 所以想发布一个解决方案。
The below demonstration is to Search for a VALUE in all COLUMNS of all TABLES in an entire SCHEMA:
下面的演示是在整个 SCHEMA 中的所有 TABLES 的所有 COLUMNS 中搜索一个值：
- Search a CHARACTER type
- 搜索CHARACTER 类型
Let's look for the value KINGin SCOTTschema.
让我们KING在SCOTT模式中寻找值。
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>
- Search a NUMERIC type
- 搜索NUMERIC 类型
Let's look for the value 20in SCOTTschema.
让我们20在SCOTT模式中寻找值。
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>
回答by Mike Rodey
I modified Flood's script to execute once for each table rather than for every column of each table for faster execution. It requires Oracle 11g or greater.
我将 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;
/
回答by bekur
I was having following issues for @Lalit Kumars answer,
对于@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
Solution is:
解决办法是：
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"
/
回答by diciu
I would do something like this (generates all the selects you need). You can later on feed them to sqlplus:
我会做这样的事情（生成你需要的所有选择）。您可以稍后将它们提供给 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 }' | while read nw;
do echo "select * from $sw where $nw='val'";
done;
done;
It yields:
它产生：
select * from TBL1 where DESCRIPTION='val'
select * from TBL1 where ='val'
select * from TBL2 where Name='val'
select * from TBL2 where LNG_ID='val'
And what it does is - for each table_namefrom user_tablesget each field (from desc) and create a select * from table where field equals 'val'.
它的作用是 - 对于每个table_namefromuser_tables获取每个字段（来自 desc）并创建一个 select * from table，其中字段等于'val'。
回答by umesh
if we know the table and colum names but want to find out the number of times string is appearing for each schema:
如果我们知道表名和列名，但想找出每个模式出现字符串的次数：
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;
回答by Alexandru
Modifying the code to search case-insensitively using a LIKE query instead of finding exact matches...
修改代码以使用 LIKE 查询不区分大小写进行搜索，而不是查找精确匹配项...
DECLARE
match_count INTEGER;
-- Type the owner of the tables you want to search.
v_owner VARCHAR2(255) :='USER';
-- Type the data type you're looking for (in CAPS). Examples include: VARCHAR2, NUMBER, etc.
v_data_type VARCHAR2(255) :='VARCHAR2';
-- Type the string you are looking for.
v_search_string VARCHAR2(4000) :='Test';
BEGIN
dbms_output.put_line( 'Starting the search...' );
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 LOWER('%'||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;
