0

Is it possible to scan through every table in a schema for a particular value in Oracle? , small change in the logic , i need to search a specific column (SAMPLE_ABC) in the schema so that i can do research further in which all tables this column SAMPLE_ABC is being used so that i can find the desired result

basically i am searching for a number example column_name - value-12345

i have tried the below but it's running for a very long time ....... , i have tried searching for options but i was not able to 

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 '12345';

        IF match_count > 0 THEN
          dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
        END IF;

      END LOOP;

    END;
    /
Data2explore
  • 452
  • 6
  • 16
  • Is it possible, yes. Is it practical, NO. To have any chance of this running, you need to further limit the schema list. There are probably at least 10-15 additional schemas in addition to SYS that do not contain user data. – thatjeffsmith May 06 '20 at 19:23
  • Assuming that most `SELECT`s you generate will be full table scans anyway, it might be *much* faster to change the logic of your PL/SQL block to generate one `SELECT` per table (instead of one per column), so that looks like this: `SELECT COUNT(*) FROM t WHERE a=:1 or b=:1 or c=:1 or...`; if you need detailed infos about the columns, change the `COUNT(*)` to `SUM(CASE when a=:1 then 1 else 0) cnt_a, SUM(CASE when b=:1 then 1 else 0) cnt_b, ...` – Erich Kitzmueller May 07 '20 at 08:59
  • you can refer this answer where a table is searched only once https://stackoverflow.com/a/9614022/1297792 – psaraj12 May 07 '20 at 16:31

1 Answers1

0

If you don't want to count appearances of the search string, EXISTS might be a better option as it'll stop searching through the table as soon as it finds the first appearance. So, I'd suggest something like this; see if it helps.

SQL> declare
  2     match_count    integer;
  3     search_string  varchar2 (20) := 'MANAGER';
  4     l_str          varchar2 (1000);
  5  begin
  6     for t in (select owner, table_name, column_name
  7                 from all_tab_columns
  8                where     owner <> 'SYS'
  9                      and owner in ('SCOTT', 'MIKE')
 10                      and data_type like '%CHAR%')
 11     loop
 12        l_str :=
 13              'SELECT MAX(1) FROM '
 14           || t.owner
 15           || '.'
 16           || t.table_name
 17           || ' a'
 18           || '  WHERE EXISTS (SELECT NULL FROM '
 19           || t.owner
 20           || '.'
 21           || t.table_name
 22           || ' b'
 23           || '                WHERE b.'
 24           || t.column_name
 25           || ' = a.'
 26           || t.column_name
 27           || '                AND b.'
 28           || t.column_name
 29           || ' = :1'
 30           || ')';
 31
 32        execute immediate l_str into match_count using search_string;
 33
 34        if match_count > 0
 35        then
 36           dbms_output.put_line (
 37              t.table_name || '.' || t.column_name || ' contains "' || search_string || '"');
 38        end if;
 39     end loop;
 40  end;
 41  /
EMP.JOB contains "MANAGER"

PL/SQL procedure successfully completed.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • i need to search a numeric value from your code you have mentioned data_type like '%CHAR%' – Data2explore May 07 '20 at 08:46
  • 1
    I copied that part from **your** code, posted along with the question. – Littlefoot May 07 '20 at 08:47
  • i have tried your query it is not stopping at all , is there way to enhance ? – Data2explore May 07 '20 at 16:41
  • Well, you're searching through all tables available to you, so ... that *promises* to be slow. Can you further restrict number of users you're searching? Currently, you omit only SYS. See what `select distinct owner from all_tables` returns are remove other users you aren't interested in. For example, for me, it returns users as APEX_040000, MDSYS, CTXSYS and such, and I certainly don't expect to find e.g. KING in there. – Littlefoot May 07 '20 at 18:22