[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
  

Mensagemem Seg, 02 Jun 2008 11:05 am

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.

Código: Selecionar todos
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;
ricardorauber
Localização: Canoas RS

Mensagemem Seg, 02 Jun 2008 11:30 am

Show de bola ! :-o
dr_gori
Localização: Portland, OR USA

Thomas F. G

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

Mensagemem Ter, 03 Jun 2008 4:18 pm

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:
gokden
Localização: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Mensagemem Sex, 06 Jun 2008 3:12 pm

gokden

Pra isso é simples, basta alterar os cursores adicionando cláusulas WHERE!
ricardorauber
Localização: Canoas RS

Mensagemem Sex, 01 Ago 2008 5:21 pm

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:
alef
Localização: Patos de Minas - MG

Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

Mensagemem Seg, 26 Abr 2010 10:04 am

bacana.....me ajudou muito!!!Vlw
RJG
Localização: Patos de Minas - MG



Voltar para SQL

Quem está online

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