Pesquisar todos os campos em todas as tabelas para um valor específico (Oracle)

É possível procurar em todos os campos de cada tabela por um valor específico no Oracle?

Existem centenas de tabelas com milhares de linhas em algumas tabelas, então sei que isso pode levar muito tempo para consultar. Mas a única coisa que sei é que um valor para o campo que gostaria de consultar é 1/22/2008P09RR8 . <

Eu tentei usar esta declaração abaixo para encontrar uma coluna apropriada com base no que eu acho que deve ser nomeado, mas não retornou nenhum resultado.

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

Não há absolutamente nenhuma documentação neste database e não tenho idéia de onde esse campo está sendo extraído.

Alguma ideia?

Citar:

Eu tentei usar esta declaração abaixo para encontrar uma coluna apropriada baseada no que eu acho que deveria ser nomeada, mas ela não retornou nenhum resultado. *

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

Uma coluna não é um object. Se você quer dizer que espera que o nome da coluna seja como ‘% DTN%’, a consulta desejada é:

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

Mas se a string ‘DTN’ é apenas um palpite de sua parte, isso provavelmente não ajudará.

By the way, como você está certo que ‘1/22 ​​/ 2008P09RR8’ é um valor selecionado diretamente de uma única coluna? Se você não sabe de onde vem, pode ser uma concatenação de várias colunas, ou o resultado de alguma function, ou um valor localizado em um object de tabela aninhada. Então você pode estar em uma perseguição selvagem tentando verificar cada coluna para esse valor. Você não pode começar com qualquer aplicativo cliente que esteja exibindo esse valor e tentar descobrir qual consulta ele está usando para obtê-lo?

De qualquer forma, a resposta do diciu fornece um método para gerar consultas SQL para verificar cada coluna de cada tabela quanto ao valor. Você também pode fazer coisas semelhantes inteiramente em uma session SQL usando um bloco PL / SQL e um SQL dynamic. Aqui está um código escrito apressadamente para isso:

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

Existem algumas maneiras de torná-lo mais eficiente também.

Nesse caso, dado o valor que você está procurando, é possível eliminar claramente qualquer coluna que seja do tipo NUMBER ou DATE, o que reduziria o número de consultas. Talvez até restringir a colunas onde o tipo é como ‘% CHAR%’.

Em vez de uma consulta por coluna, você poderia criar uma consulta por tabela como esta:

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

Fiz algumas modificações no código acima para que ele funcione mais rápido se você estiver pesquisando em apenas um proprietário. Você só precisa alterar as 3 variables ​​v_owner, v_data_type e v_search_string para se adequar ao que você está procurando.

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

Sim, você pode e seu DBA vai odiá-lo e vai encontrá-lo para pregar seus sapatos para o chão, porque isso vai causar muita E / S e trazer o desempenho do database realmente para baixo como o cache expurga.

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

para começar.

Eu começaria com as consultas em execução, usando a v$session e a v$sqlarea . Isso muda com base na versão oracle. Isso diminuirá o espaço e não atingirá tudo.

Aqui está outra versão modificada que irá comparar uma correspondência de substring inferior. Isso funciona no 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; / 

Eu sei que este é um tópico antigo. Mas vejo um comentário na pergunta perguntando se isso poderia ser feito em SQL em vez de usar PL/SQL . Então pensei em postar uma solução.

A demonstração abaixo é Procurar por um VALOR em todas as COLUNAS de todas as TABELAS em um SCHEMA inteiro :

  • Pesquisar um tipo CHARACTER

Vamos procurar o valor KING no esquema 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> 
  • Pesquisar um tipo NUMERIC

Vamos procurar o valor 20 no esquema 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> 

Eu faria algo assim (gera todos os selects que você precisa). Você pode depois alimentá-los com o 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; 

Ela produz:

 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 o que ele faz é – para cada table_name de user_tables pegue cada campo (de desc) e crie um select * da tabela onde field é igual a ‘val’.

se soubermos os nomes da tabela e da coluna, mas quisermos descobrir o número de vezes que a string está aparecendo para cada esquema:

 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; 

Eu modifiquei o script do Flood para executar uma vez para cada tabela em vez de para cada coluna de cada tabela para execução mais rápida. Requer o Oracle 11g ou superior.

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

Procedimento para pesquisar o database inteiro:

  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; 

Executar declaração

  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; 

Resultados da Amostra

 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. 

Modificando o código para pesquisar sem distinção entre maiúsculas e minúsculas usando uma consulta LIKE em vez de encontrar correspondências exatas …

 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; 

Eu não faço de uma solução simples no promprt SQL. No entanto, existem algumas ferramentas como sapo e PL / SQL Developer que tem uma GUI onde um usuário pode inserir a string a ser pesquisada e retornará a tabela / procedimento / object onde esta é encontrada.

Existem algumas ferramentas gratuitas que fazem este tipo de pesquisa, por exemplo, esta funciona bem e o código fonte está disponível: https://sites.google.com/site/freejansoft/dbsearch

Você precisará do driver ODBC do Oracle e de um DSN para usar essa ferramenta.

–é executado concluído – sem erro

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

Emprestando, melhorando e simplificando um pouco desta postagem do Blog, a seguinte declaração SQL simples parece fazer o trabalho muito bem:

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

Eu estava tendo os seguintes problemas para a resposta do @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 

Solução é:

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