[Dica] Procurar um valor em TODAS as tabelas

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
ricardorauber
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 96
Registrado em: Qua, 21 Jun 2006 11:33 pm
Localização: Canoas RS

Procedimento para consultar em TODAS as tabelas do banco onde existe um certo valor

Observações:
- Criei este procedimento pois as vezes precisamos saber algo do tipo "onde será que o sistema usa o ID 177?" ou então "onde será que o sistema cadastrou a descrição Fulano01?"
- Basta substituir os campos p_* e executar sendo que o resultado sairá no Output.
- Caso esteja utilizando um usuário que seja o owner das tabelas, fica melhor mudar os cursores para USER_TABLES e USER_TAB_COLUMNS ao invés de DBA_TABLES e DBA_TAB_COLUMNS pois assim vai eliminar muitas consultas desnecessárias em tabelas de outros esquemas.
- Este é um procedimento que passa por todas as tabelas, então não espere que seja rápido.

Selecionar tudo

DECLARE

  --========================================================--
  -- PARAMETROS
  --========================================================--

  p_tipo_campo VARCHAR2(160) := 'VARCHAR2'; -- Tipo de valor (VARCHAR2, NUMBER, DATE...)
  p_parametro  VARCHAR2(256) := 'CPG'; -- Valor a consultar
  p_comparacao VARCHAR2(10)  := '='; -- '=' ou 'LIKE'

  --========================================================--
  -- TIPOS
  --========================================================--

  TYPE ref_cursor IS REF CURSOR;
  c_cursor ref_cursor;

  --========================================================--
  -- CURSORES
  --========================================================--

  -- Tabelas
  CURSOR c_tabs IS
    SELECT owner,
           table_name
      FROM dba_tables;
  v_owner  dba_tables.owner%TYPE;
  v_tabela dba_tables.table_name%TYPE;

  -- Colunas
  CURSOR c_cols(pc_owner      dba_tables.owner%TYPE, 
                pc_tabela     dba_tables.table_name%TYPE, 
                pc_tipo_campo dba_tab_columns.data_type%TYPE) IS
    SELECT column_name
      FROM dba_tab_columns u
     WHERE table_name = pc_tabela
       AND owner = pc_owner
       AND u.data_type = pc_tipo_campo;
  v_coluna dba_tab_columns.column_name%TYPE;

  --========================================================--
  -- VARIAVEIS GERAIS
  --========================================================--

  v_sql VARCHAR2(1000);
  v_aux VARCHAR2(300);

BEGIN

  --========================================================--
  -- Busca as tabelas
  --========================================================--

  OPEN c_tabs;
  LOOP
    FETCH c_tabs
      INTO v_owner, v_tabela;
    EXIT WHEN c_tabs%NOTFOUND;
  
    --========================================================--
    -- Busca as colunas
    --========================================================--
  
    OPEN c_cols(v_owner,
                v_tabela,
                p_tipo_campo);
    LOOP
      FETCH c_cols
        INTO v_coluna;
      EXIT WHEN c_cols%NOTFOUND;
    
      --========================================================--
      -- Se os campos são VARCHAR2 então coloca tudo em maiúsculo
      --========================================================--
    
      IF p_tipo_campo = 'VARCHAR2' THEN
        v_coluna    := upper(v_coluna);
        p_parametro := upper(p_parametro);
      END IF;
    
      --========================================================--    
      -- Alimenta o select do cursor
      --========================================================--
    
      v_sql := 'SELECT ' || v_coluna || ' FROM ' || v_tabela || ' WHERE ' || v_coluna || ' ' ||
               p_comparacao || ' :p_parametro';
    
      --========================================================--
      -- Faz a consulta
      --========================================================--
    
      BEGIN
        OPEN c_cursor FOR v_sql
          USING p_parametro;
        FETCH c_cursor
          INTO v_aux;
      
        --========================================================--
        -- Se encontrou, mostra no output
        --========================================================--
      
        IF c_cursor%FOUND THEN
          dbms_output.put_line(v_tabela || '.' || v_coluna || ' = ' || v_aux);
        END IF;
      
        CLOSE c_cursor;
      EXCEPTION
        WHEN OTHERS THEN
          CLOSE c_cursor;
          --dbms_output.put_line(sqlerrm); /* Descomentar se quiser ver o erro */
      END;
    
    END LOOP;
    CLOSE c_cols; -- Colunas
  
  END LOOP;
  CLOSE c_tabs; -- Tabelas

END;
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Show de bola ! :-o
gokden
Rank: DBA Pleno
Rank: DBA Pleno
Mensagens: 264
Registrado em: Dom, 19 Ago 2007 8:18 pm
Localização: Ribeirão Preto - SP
Lucas de Souza

OCA Developer
Analista de sistemas

Boa dica mesmo cara....
teve um dia que eu precisei de algo assim.. mais foi com Constraints, tive que achar tadas as tabelas que tinham um campo tal como FK....

o ruim desse select é qui ele vai correr todas as tabelas do banco mesmo, ia ser legal se tivesse uma jeito de invés de procurar em tudo, o usuário colocasse o nome de algumas tabelas qui ele pensa que tem este valor, ou o nome do campo qui tem o valor procurado.... :wink:
ricardorauber
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 96
Registrado em: Qua, 21 Jun 2006 11:33 pm
Localização: Canoas RS

gokden

Pra isso é simples, basta alterar os cursores adicionando cláusulas WHERE!
Avatar do usuário
alef
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 119
Registrado em: Ter, 06 Nov 2007 2:45 pm
Localização: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

ai é show!!! muito show mesmo!!!

fiz os testes aqui 100% aprovado!!!!


ehehhehe

:-o :-o :-o :-o :-o :-o :-o :-o :idea: :idea: :idea: :idea: :idea:
RJG
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 79
Registrado em: Ter, 16 Mai 2006 11:40 am
Localização: Patos de Minas - MG

bacana.....me ajudou muito!!!Vlw
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 17 visitantes