在所有表中的所有字段中搜索特定值(Oracle)

是否可以在 Oracle 中搜索每个表的每个字段以获得特定的值？

有些表中有数百个表，有数千行，所以我知道这可能需要很长的时间来查询。但是我唯一知道的是，我想查询的字段的值是 1/22/2008P09RR8。 <

我已经尝试使用下面的语句根据我认为应该命名的内容找到一个合适的列，但是没有返回任何结果。

SELECT * from dba_objects
WHERE object_name like '%DTN%'

这个数据库里没有任何文档，我也不知道这个字段是从哪里来的。

有什么想法吗？

502369 次浏览
小开

我没有一个简单的解决方案的 SQL 提示。然而，有相当多的工具，如 toad 和 PL/SQL Developer，它们都有一个 GUI，用户可以在其中输入要搜索的字符串，然后返回表/过程/对象。

小开

是的，你可以，你的 DBA 会讨厌你，会发现你把你的鞋钉在地板上，因为这将导致大量的 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 版本。这样可以缩小空间，不会碰到所有东西。

小开

我会这样做(生成所有您需要的选择)。 以后你可以把它们喂给 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，获取每个字段(来自 desc) ，并从字段等于‘ val’的表中创建一个 select * 。

小开
最佳答案

语录:

我在下面尝试使用这个语句 根据以下内容查找适当的列 我认为它应该被命名，但它 没有结果。 * 

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 查询的方法来检查每个表的每一列的值。您还可以使用 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;
/

还有一些方法可以让它更有效率。

在这种情况下，给定您要查找的值，您可以清楚地消除任何 NUMBER 或 DATE 类型的列，这将减少查询的数量。甚至可以将其限制为类型类似于“% CHAR%”的列。

您可以像下面这样为每个表构建一个查询，而不是每列构建一个查询:

SELECT * FROM table1
WHERE column1 = 'value'
OR column2 = 'value'
OR column3 = 'value'
...
;
小开

有一些免费的工具可以进行这种搜索，例如，这个工具可以很好地工作，源代码也是可用的: Https://sites.google.com/site/freejansoft/dbsearch

使用此工具需要 Oracle ODBC 驱动程序和 DSN。

小开

如果您只在一个所有者中搜索，我对上面的代码做了一些修改，以使其工作得更快。 您只需更改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;
/
小开

下面是另一个修改后的版本，它将比较较低的子字符串匹配。

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;
/
小开

搜索整个数据库的程序:

    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.
小开

我修改了 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;
/
小开

如果我们知道表和列的名称，但是想知道每个模式出现字符串的次数:

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;
小开

我知道这是个老话题了。但是，我看到一个评论的问题，如果它可以做的 SQL而不是使用 PL/SQL。所以想到发布一个解决方案。

下面是对 在整个 SCHEMA 中的所有表的所有列中搜索一个值的演示:

  • 搜索 性格类型

让我们在 SCOTT模式中查找值 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>
小开

- 运行完毕-没有错误

    SET SERVEROUTPUT ON SIZE 100000


DECLARE
v_match_count     INTEGER;
v_counter         INTEGER;








v_owner           VARCHAR2 (255) := 'VASOA';
v_search_string   VARCHAR2 (4000) := '99999';
v_data_type       VARCHAR2 (255) := 'CHAR';
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, table_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;


IF cur_columns.column_name is not null
THEN
v_sql :=
v_sql
|| 'upper('
|| cur_columns.column_name
|| ') ='''
|| UPPER (v_search_string)||'''';


v_counter := v_counter + 1;
END IF;


END LOOP;


IF v_sql is  null
THEN
v_sql :=
'select count(*) from '
|| v_owner
|| '.'
|| cur_tables.table_name;


END IF;


IF v_sql is not null
THEN
v_sql :=
'select count(*) from '
|| v_owner
|| '.'
|| cur_tables.table_name
|| ' where '
|| v_sql;
END IF;


--v_sql := 'select count(*) from ' ||v_owner||'.'|| cur_tables.table_name ||' where '||  v_sql;




--dbms_output.put_line(v_sql);
--DBMS_OUTPUT.put_line (v_sql);


EXECUTE IMMEDIATE v_sql INTO v_match_count;


IF v_match_count > 0
THEN
DBMS_OUTPUT.put_line (v_sql);
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;
/
小开

修改代码以搜索大小写-不敏感地使用 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;
小开

从 这篇博文中借用、略微增强和简化以下简单的 SQL 语句似乎可以很好地完成这项工作:

SELECT DISTINCT (:val) "Search Value", TABLE_NAME "Table", COLUMN_NAME "Column"
FROM cols,
TABLE (XMLSEQUENCE (DBMS_XMLGEN.GETXMLTYPE(
'SELECT "' || COLUMN_NAME || '" FROM "' || TABLE_NAME || '" WHERE UPPER("'
|| COLUMN_NAME || '") LIKE UPPER(''%' || :val || '%'')' ).EXTRACT ('ROWSET/ROW/*')))
ORDER BY "Table";
小开

我正在为“ Lalit Kumar 的回答,

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"
/
小开

我找到了最好的解决方案，但是有点慢

SELECT DISTINCT table_name, column_name, data_type
FROM user_tab_cols,
TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
|| column_name
|| ' from '
|| table_name
|| ' where lower('
|| column_name
|| ') like lower(''%'
|| 'your_text_here'
|| '%'')' ).extract ('ROWSET/ROW/*') ) ) a
where table_name not in (
select distinct table_name
from user_tab_cols where data_type like 'SDO%'
or data_type like '%LOB') AND DATA_TYPE = 'VARCHAR2'
order by table_name, column_name;
小开

OracleLike 条件允许在 SELECT、 INSERT、 UPDATE 或 DELETE 语句的 WHERE 子句中使用通配符。

% : 匹配任何长度的字符串

例如

SELECT last_name
FROM   customer_tab
WHERE  last_name LIKE '%A%';

: 匹配一个字符

例如

SELECT last_name
FROM   customer_tab
WHERE  last_name LIKE 'A_t';

提交答案