Cerca tutti i campi in tutte le tabelle per un valore specifico (Oracle)

È ansible cercare ogni campo di ogni tabella per un valore particolare in Oracle?

Ci sono centinaia di tabelle con migliaia di righe in alcune tabelle, quindi so che potrebbe richiedere molto tempo per eseguire una query. Ma l’unica cosa che so è che un valore per il campo su cui vorrei interrogare è 1/22/2008P09RR8 . <

Ho provato a utilizzare questa istruzione di seguito per trovare una colonna appropriata in base a ciò che penso dovrebbe essere denominata, ma non ha restituito risultati.

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

Non c’è assolutamente alcuna documentazione su questo database e non ho idea da dove viene tirato questo campo.

qualche idea?

Citazione:

Ho provato a utilizzare questa istruzione di seguito per trovare una colonna appropriata in base a ciò che penso dovrebbe essere denominata, ma non ha restituito risultati. *

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

Una colonna non è un object. Se vuoi dire che ti aspetti che il nome della colonna sia come ‘% DTN%’, la query che vuoi è:

 SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DTN%'; 

Ma se la stringa ‘DTN’ è solo un’ipotesi da parte tua, probabilmente non ti sarà d’aiuto.

A proposito, quanto sei sicuro che “1/22 / 2008P09RR8” sia un valore selezionato direttamente da una singola colonna? Se non si conosce affatto da dove proviene, potrebbe trattarsi di una concatenazione di più colonne, o il risultato di qualche funzione, o un valore che si trova in un object di tabella annidato. Quindi potresti essere a caccia di un’oca selvaggia cercando di controllare ogni colonna per quel valore. Non si può iniziare con qualsiasi applicazione client sta visualizzando questo valore e cercare di capire quale query sta usando per ottenerlo?

In ogni caso, la risposta di diciu fornisce un metodo per generare query SQL per controllare ogni colonna di ogni tabella per il valore. Puoi anche fare cose simili interamente in una sessione SQL usando un blocco PL / SQL e SQL dinamico. Ecco un codice scritto in fretta per questo:

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

Ci sono alcuni modi per renderlo più efficiente.

In questo caso, dato il valore che stai cercando, puoi eliminare chiaramente qualsiasi colonna che sia di tipo NUMBER o DATE, il che ridurrebbe il numero di query. Forse anche limitarlo a colonne in cui il tipo è come ‘% CHAR%’.

Invece di una query per colonna, potresti creare una query per tabella in questo modo:

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

Ho apportato alcune modifiche al codice precedente per farlo funzionare più velocemente se stai cercando in un solo proprietario. Devi solo modificare le 3 variabili v_owner, v_data_type e v_search_string per adattarle a ciò che stai cercando.

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

Sì, puoi farlo e il tuo DBA ti odierà e ti troverà a inchiodare le scarpe a terra perché ciò causerà un sacco di I / O e porterà le prestazioni del database davvero al minimo mentre la cache si spegne.

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

per iniziare.

Vorrei iniziare con le query in esecuzione, utilizzando la v$session e v$sqlarea . Questo cambia in base alla versione di Oracle. Questo restringerà lo spazio e non colpirà tutto.

Ecco un’altra versione modificata che confronterà una corrispondenza di sottostringa inferiore. Funziona in 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; / 

So che questo è un vecchio argomento. Ma vedo un commento alla domanda che chiede se si potrebbe fare in SQL piuttosto che usare PL/SQL . Quindi pensato per pubblicare una soluzione.

La seguente dimostrazione è di cercare un VALORE in tutte le COLONNE di tutte le TABELLE in un intero SCHEMA :

  • Cerca un tipo di PERSONAGGIO

Cerchiamo il valore KING nello schema 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> 
  • Cerca un tipo NUMERICO

Cerchiamo il valore 20 nello schema 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> 

Farei qualcosa di simile (genera tutti i selezioni necessari). Puoi in seguito dar loro da mangiare a sqlplus:

 echo "select table_name from user_tables;" | sqlplus -S user/pwd | grep -v "^--" | grep -v "TABLE_NAME" | grep "^[AZ]" | 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; 

Produce:

 select * from TBL1 where DESCRIPTION='val' select * from TBL1 where ='val' select * from TBL2 where Name='val' select * from TBL2 where LNG_ID='val' 

E quello che fa è – per ogni table_name da user_tables ottenere ogni campo (da desc) e creare un select * dalla tabella in cui il campo è uguale a ‘val’.

se conosciamo i nomi di tabelle e colum ma vogliamo scoprire il numero di volte che la stringa appare per ogni 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; 

Ho modificato lo script di Flood per l’esecuzione una volta per ogni tabella piuttosto che per ogni colonna di ogni tabella per un’esecuzione più rapida. Richiede Oracle 11g o versioni successive.

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

Procedura per cercare l’intero database:

  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; 

Esegui dichiarazione

  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; 

Risultati del campione

 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. 

Modifica del codice per cercare senza distinzione tra maiuscole e minuscole, utilizzando una query LIKE invece di trovare corrispondenze esatte …

 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; 

Non ho una soluzione semplice sul promprt SQL. Comunque ci sono alcuni strumenti come Toad e PL / SQL Developer che hanno una GUI in cui un utente può inserire la stringa da cercare e restituirà la tabella / procedura / object dove questo viene trovato.

Esistono alcuni strumenti gratuiti che rendono questo tipo di ricerca, ad esempio, questo funziona correttamente e il codice sorgente è disponibile: https://sites.google.com/site/freejansoft/dbsearch

Avrai bisogno del driver Oracle ODBC e di un DSN per utilizzare questo strumento.

– Esecuzione completata – Nessun errore

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

Prendendo in prestito, leggermente migliorando e semplificando da questo post sul blog la seguente semplice istruzione SQL sembra fare abbastanza bene il lavoro:

 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"; 

Stavo seguendo i seguenti problemi per la risposta di @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 

La soluzione è:

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