[Hint] Find a value in all tables

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Mon, 02 Jun 2008 11:05 am

Procedure to consult on all tables in the where there is a certain value remarks:-I created this procedure because sometimes we need to know something \"I wonder where the system uses the 177 ID?\" or \"where does the system registered the description Fulano01?\"-just replace the fields p _ * and run and the result will come out in the Output.
-If you are using a user who is the owner of the tables, it's better to change the cursors for USER_TABLES and USER_TAB_COLUMNS instead of DBA_TABLES and DBA_TAB_COLUMNS because it will eliminate many unnecessary queries on tables of other schemas.
[color = red]-This is a procedure that goes through all the tables, so don't expect it to be fast.[/color]

Code: Select all
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
Location: Canoas RS

Poston Mon, 02 Jun 2008 11:30 am

Show de bola! :-o
dr_gori
Location: 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

Poston Tue, 03 Jun 2008 4:18 pm

Good tip even face .... had a day that I needed something like this. more was with Constraints, had to find all tables that had a field such as FK. ... the bad of this select is qui he will run all the tables in the same, it would be nice if it had a way to instead of looking at all, the user puts the name of some tables qui he thinks he has this value, or the field name qui has the value sought. ... : wink:
gokden
Location: Ribeirão Preto - SP

Lucas de Souza

OCA Developer
Analista de sistemas

Poston Fri, 06 Jun 2008 3:12 pm

gokden for this is simple, just change the cursors add WHERE clauses.
ricardorauber
Location: Canoas RS

Poston Fri, 01 Aug 2008 5:21 pm

ai's show!!! very nice same!

did the tests here 100% approved!!


:-o:-o:-o:-o:-o ehehhehe:-o:-o:-o: idea:: idea:: idea:: idea:: idea:
alef
Location: Patos de Minas - MG

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

Poston Mon, 26 Apr 2010 10:04 am

cool ... helped me a lot!!!Vlw
RJG
Location: Patos de Minas - MG



Return to SQL

Who is online

Users browsing this forum: No registered users and 4 guests