GTT (3) - BD MUNICIPIO (3). Encontrar las tablas y campos que contienen un valor concreto

0.    Introducción

OJO: DBeaver version 23.2.5 FALLA en estos scripts. Utilizar la versión 23.3.4  

1. Si no queremos que aparezcan las tablas vacias

2. Busccar un literal

3. Buscar un número

4. Buscar 2 literales en una misma tabla

5. Buscar 2 números en una misma tabla

1. Si no queremos que aparezcan las tablas vacias

Para ello hay que cambiar en las consultas de los apartados posteriores 

FOR t IN (
  SELECT al.table_name, al.column_name
  FROM all_tab_cols al
  WHERE al.owner = v_owner 
    AND table_name NOT LIKE 'TMP%'
    AND table_name NOT LIKE 'XTMP%'
    AND table_name NOT LIKE 'ESTR%'
    AND table_name NOT LIKE 'LUCA%'

Por

FOR t IN (
  SELECT al.table_name, al.column_name
  FROM all_tab_cols al
  JOIN XIMO_SCHM.EDU_NUM_REGS_TABLA edu
    ON edu.TABLE_NAME = al.TABLE_NAME
   AND edu.NUM_ROWS >0 
   WHERE al.owner = v_owner 

Ya que para ello creamos la tabla XIMO_SCHM.EDU_NUM_REGS_TABLA con el fin de encontrar las tablas vacias

2. Buscar un literal

Hay que advertir que este proceso es muy lento!!! Tarda alrededor de 4 minutos 

 Queremos encontrar aquellas tablas que contienen un DNI por ejemplo "12345678S"

Según StackOverflow en esta entrada y esta otra, la consulta a ejecutar es:

DECLARE match_count INTEGER;
    v_owner all_tab_cols.owner%type  := 'OPS$GTTORA';
    --- v_search_string VARCHAR2(4000) := '12345678S';
    v_search_string VARCHAR2(4000) := '%A00002789703%';
    comentari varchar2(4000) :='';

BEGIN
    FOR t IN (SELECT table_name, column_name
        FROM all_tab_cols
        WHERE owner = v_owner 
          AND table_name NOT LIKE 'TMP%'
          AND table_name NOT LIKE 'XTMP%'
          AND table_name NOT LIKE 'ESTR%'
          AND table_name NOT LIKE 'LUCA%'
          AND data_type IN ('CHAR',
                            'VARCHAR2',
                            'NCHAR',
                            'NVARCHAR2')
        ORDER BY table_name, column_name )
    LOOP

        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '|| v_owner ||'.'||t.table_name
        --  ||' WHERE '||t.column_name||' = :1'
            ||' WHERE '||t.column_name||' LIKE :1'

       INTO match_count
       USING v_search_string;

       IF match_count > 0 THEN
          --begin edu
           EXECUTE IMMEDIATE 'SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE OWNER='''||v_owner ||''' AND TABLE_NAME = :1'
           INTO comentari
           USING t.table_name;
           --end edu--

           dbms_output.put_line(v_owner ||'.'||t.table_name ||' WHERE '||t.column_name
               ||' LIKE '''||v_search_string ||''' ;--->'|| match_count ||' '||comentari);

       END IF;
    END LOOP;
END;
-- Si se ejecuta desde DBeaver no colocar la "/" siguiente. 
-- Si se ejecuta en Oracle SQL Plus si que hay que colocar la "/" siguiente
-- ver 
--/    

3. Buscar un número

En el caso que queramos buscar números por ejemplo 5637

Podemos hacer

DECLARE match_count INTEGER;
    v_owner all_tab_cols.owner%type  := 'OPS$GTTORA';
    v_search_number NUMBER (10,0) := 5637;
    comentari varchar2(4000) :='';
BEGIN
    FOR t IN (SELECT al.table_name, al.column_name
        FROM all_tab_cols al
        JOIN XIMO_SCHM.EDU_NUM_REGS_TABLA edu
          ON edu.TABLE_NAME = al.TABLE_NAME
         AND edu.NUM_ROWS >0 
        WHERE al.owner = v_owner 
          --AND table_name NOT LIKE 'TMP%'
          --AND table_name NOT LIKE 'XTMP%'
          --AND table_name NOT LIKE 'ESTR%'
          --AND table_name NOT LIKE 'LUCA%'
          AND al.data_type IN ( 'NUMBER') 
                     ---          , 'LONG') --ojo tipo LONG es caracter!!!
         ORDER BY al.table_name, al.column_name  )                 
    LOOP

        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||v_owner ||'.'||t.table_name
            ||' WHERE '||t.column_name||' = :1'
        INTO match_count
        USING v_search_number;

        IF match_count > 0 THEN
           --begin edu
           EXECUTE IMMEDIATE 'SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE OWNER='''||v_owner ||''' AND TABLE_NAME = :1'
           INTO comentari
           USING t.table_name;
           --end edu--

           dbms_output.put_line(v_owner ||'.'||t.table_name ||' WHERE '||t.column_name ||'='||v_search_number
               ||';---> '||match_count ||' '|| comentari);
        END IF;
    END LOOP;
END;
-- Si se ejecuta desde DBeaver no colocar la "/" siguiente. 
-- Si se ejecuta en Oracle SQL Plus si que hay que colocar la "/" siguiente
-- ver 
--/    

Y nos devuelve en una ventana de DBeaver OUTPUT en este último caso:

ANCO_ANOTACIONES_CONTABLES ID_ANCO 1
EPER_ELEMENTOS_PERSONA ID_EPER 1
LSOT_LINEAS_SOP_TRAFICO_IVTM ID_EPER_OT 2
OTIV_OBJETO_TRIBUTARIO_IVTM ID_EPER_OT 3
OT_OBJETO_TRIBUTARIO ID_EPER_OT 1
OTPT_OBJETOS_TRIB_PREV_VOLU ID_EPER_OTPT 8
VAIV_VALORES_IVTM ID_EPER_OT 5

4. Buscar 2 literales en una misma tabla

Y para el caso que queramos buscar tablas que contengan 2 strings

DECLARE match_count INTEGER;
    v_owner all_tab_cols.owner%type  := 'OPS$GTTORA';
    v_search_string1 VARCHAR2(4000) := '%CAR%5%';
    v_search_string2 VARCHAR2(4000) := '%73912%';
    v_count1 NUMBER (10,0) := 0;
    v_count2 NUMBER (10,0) := 0;
    v_col1 VARCHAR2(4000) := '';
    v_col2 VARCHAR2(4000) := '';
    comentari varchar2(4000) :='';

    
    
BEGIN
    FOR t IN (SELECT table_name, column_name
        FROM all_tab_cols
        WHERE owner = v_owner 
          AND table_name NOT LIKE 'TMP%'
          AND table_name NOT LIKE 'XTMP%'
          AND table_name NOT LIKE 'ESTR%'
          AND table_name NOT LIKE 'LUCA%'
          AND data_type IN ('CHAR',
                            'VARCHAR2',
                            'NCHAR',
                            'NVARCHAR2')
        ORDER BY table_name, column_name  )                 
    LOOP
        v_count1 := 0;
        v_count2 := 0;
       
        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '|| v_owner ||'.'||t.table_name||' WHERE '||t.column_name||' LIKE :1'
        INTO match_count
        USING v_search_string1;
       
        IF match_count > 0 THEN
           v_count1 := match_count;
           v_col1 := t.column_name;
        END IF;
       
        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '|| v_owner ||'.'||t.table_name||' WHERE '||t.column_name||' LIKE :1'
        INTO match_count
        USING v_search_string2;
       
        IF match_count > 0 THEN
           --begin edu
           --EXECUTE IMMEDIATE 'SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE TABLE_NAME = :1'
           --INTO comentari
           --USING t.table_name;
           --end edu--

           v_count2 := match_count;
           v_col2 := t.column_name;
        END IF;
      
        

        IF (v_count1 > 0 AND v_count2>0 ) THEN
           
           EXECUTE IMMEDIATE 'SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE OWNER='''||v_owner ||''' AND TABLE_NAME = :1'
           INTO comentari
           USING t.table_name;
          
           dbms_output.put_line( v_owner ||'.'||t.table_name ||' WHERE '||v_col1|| ' LIKE '''|| v_search_string1 || ''' ' ||
              'OR '|| v_col2 || ' LIKE '''|| v_search_string2 || ''' ' ||
              ';-->('|| v_count1 ||'),('|| v_count2 ||') --'|| comentari);
        END IF;
    END LOOP;
END;

5. Buscar 2 números en una misma tabla

Y si buscamos 2 números

--SET SERVEROUTPUT ON SIZE 100000;

DECLARE match_count INTEGER;
    v_owner all_tab_cols.owner%type  := 'OPS$GTTORA';
    v_search_number1 NUMBER (10,0) := 113397599;  --id_eper de direccio
    v_search_number2 NUMBER (10,0) := 8034110;    --id_eper de persona
    v_count1 NUMBER (10,0) := 0;
    v_count2 NUMBER (10,0) := 0;
    v_col1 VARCHAR2(4000) := '';
    v_col2 VARCHAR2(4000) := '';
    comentari varchar2(4000) :='';
    
BEGIN
    FOR t IN (SELECT table_name, column_name
        FROM all_tab_cols
        WHERE owner = v_owner 
          AND table_name NOT LIKE 'TMP%'
          AND table_name NOT LIKE 'XTMP%'
          AND table_name NOT LIKE 'ESTR%'
          AND table_name NOT LIKE 'LUCA%'
          AND data_type IN ('NUMBER')
        ORDER BY table_name, column_name  )                 
    LOOP
        v_count1 := 0;
        v_count2 := 0;
       
        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '|| v_owner ||'.'||t.table_name||' WHERE '||t.column_name||' = :1'        INTO match_count
        USING v_search_number1;
       
        IF match_count > 0 THEN
           v_count1 := match_count;
           v_col1 := t.column_name;
        END IF;
       
        EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '|| v_owner ||'.'||t.table_name||' WHERE '||t.column_name||' = :1'INTO match_count
        USING v_search_number2;
       
        IF match_count > 0 THEN
           v_count2 := match_count;
           v_col2 := t.column_name;
        END IF;
      
        IF (v_count1 > 0 AND v_count2>0 ) THEN
           EXECUTE IMMEDIATE 'SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE OWNER='''||v_owner ||''' AND TABLE_NAME = :1'
           INTO comentari
           USING t.table_name;
          
           dbms_output.put_line( v_owner ||'.'||t.table_name ||' WHERE '||v_col1|| ' = '|| v_search_number1 || 
              ' OR '|| v_col2 || ' = '|| v_search_number2 || 
              ' ;-->('|| v_count1 ||'),('|| v_count2 ||') --'|| comentari);
    END LOOP;
END;