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




search plsql database-table (13)

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

在一些表格中有成千上萬行的數百個表,所以我知道這可能需要很長時間才能查詢。 但我唯一知道的是,我想查詢的領域的價值是1/22/2008P09RR8 。 <

我試過使用下面的這條語句來找到一個合適的列,它基於我認為應該命名的內容,但是它沒有返回任何結果。

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

這個數據庫絕對沒有文檔,我不知道這個字段從哪裡被取消。

有什麼想法嗎?


Answers

有一些免費的工具可以進行這種搜索,例如,這個工具可以正常工作,源代碼可用: https://sites.google.com/site/freejansoft/dbsearchhttps://sites.google.com/site/freejansoft/dbsearch

您需要Oracle ODBC驅動程序和DSN才能使用此工具。


我會做這樣的事情(產生你需要的所有選擇)。 您稍後可以將它們提供給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)並創建一個select * from表,其中字段等於'val'。


我在@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"
/ 

是的,你可以和你的DBA會討厭你,並會發現你將鞋子釘在地板上,因為這會導致大量的I / O,並在緩存清除時使數據庫性能真正下降。

select column_name from all_tab_columns c, user_all_tables u where c.table_name = u.table_name;

作為一個開始。

我將開始運行查詢,使用v$sessionv$sqlarea 。 這根據oracle版本進行更改。 這將縮小這個空間,而不是一切。


如果我們知道表和列的名稱,但想知道每個模式出現字符串的次數:

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 promprt沒有簡單的解決方案。 Howeve有很多像Toad和PL / SQL Developer這樣的工具,它們有一個GUI,用戶可以在其中輸入要搜索的字符串,它將返回找到該字符串的表/過程/對象。


引用:

我試過使用下面的這條語句來找到一個適當的列,它基於我認為應該命名的內容,但它沒有返回任何結果。*

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

搜索整個數據庫的過程:

    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##[email protected]@DEPT##DEPTNO
Process exited.
Disconnecting from the database test.

我知道這是一個老話題。 但是我看到一個問題的評論,詢問是否可以在SQL完成而不是使用PL/SQL 。 所以想到發布解決方案。

下面的演示是在整個SCHEMA中搜索所有表中所有列的VALUE值

  • 搜索CHARACTER類型

我們來看看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>
  • 搜索NUMERIC類型

我們來看看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;
/

借用,稍微增強和簡化從這個博客文章下面的簡單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";

我對上述代碼做了一些修改,以便在只搜索一個所有者時更快地工作。 您只需更改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;
/

trunc(my_date,'DD')將只提供Oracle中的日期而不是時間。





sql oracle search plsql database-table