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

是否有可能在每个表的每个字段中搜索Oracle中的特定值？

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

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

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

这个数据库绝对没有文档,我不知道该字段的来源.

有什么想法吗？

1> Dave Costa..：

---

引用:

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

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'
     ...
     ;

---

@ammoQ - 就像我在倒数第二段中所说的那样？

2> Flood..：

---

我对上面的代码做了一些修改,如果你只搜索一个所有者,它可以更快地工作.您只需更改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;
/

3> jim..：

---

是的,你可以和你的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版本而变化.这将缩小空间范围,而不是打击一切.

4> Lalit Kumar ..：

---

我知道这是一个古老的话题.但是我看到一个问题的评论,询问是否可以用SQL而不是用PL/SQL.所以想发布一个解决方案.

以下演示是在整个SCHEMA中的所有表格的所有列中搜索值:

搜索CHARACTER类型

让我们来看看该值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>

搜索NUMERIC类型

让我们来看看该值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>

---

hrmm ....使用xml看起来像矫枉过正.除此之外:`XML处理错误ORA-00932:数据类型不一致:预期NUMBER得到了BLOB`

---

ORA-19202:XML处理错误ORA-00932:数据类型不一致:预期CHAR得到BLOB ORA-06512:在"SYS.DBMS_XMLGEN",第288行ORA-06512:第1行19202. 00000 - "XML处理出错%s"*原因:处理XML函数时发生错误*操作:检查给定的错误消息并修复相应的问题

5> 小智..：

---

这是另一个修改版本,它将比较较低的子字符串匹配.这适用于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;
/

6> diciu..：

---

我会做这样的事情(生成你需要的所有选择).您可以稍后将它们提供给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'

而他们做的是-每个table_name从user_tables获得的每个字段(从DESC),并从表中创建一个选择*,其中字段等于"VAL".