1

I am trying to find a column (ABC) and it's value 1234 from a schema , basically i need to to check if ABC and a value from this column 1234 is present in any other table that is mapped to ABC , i tried to do a search the most efficient way but it is taking lot of time and not retrieving the desired result

i have tried https://lalitkumarb.wordpress.com/2015/01/06/sql-to-search-for-a-value-in-all-columns-of-all-atbles-in-an-entire-schema/

but the query is not results at all it is running running...

Data2explore
  • 452
  • 6
  • 16

1 Answers1

0

You may write the output to a file if you get buffer overflow on set Serveroutput otherwise this should do.Output will have all tables that has 'ABC' column and respective count shows count of record with ABC column value as 1234.

SET SERVEROUTPUT ON 100000
DECLARE 
lv_count number(10):=0;
l_str    varchar2 (1000);
BEGIN 
FOR V1 IN 
(select distinct table_name 
 from dba_tab_columns 
 where column_name = 'ABC')

 LOOP

  BEGIN 
    lv_query := ' select count(*) from '||v1.table_name||' where ABC =1234';
    EXECUTE IMMEDIATE lv_query INTO lv_count;
    dbms_output.put_line(v1.table_name||' --> '||lv_count);

    EXCEPTION 
       WHEN OTHERS THEN 
         dbms_output.put_line('OTHERS EXCEPTION '||v1.table_name||' ERRCODE '||SQLERRM||' '||SUBSTR(SQLCODE,1,200));
    END; 

 END LOOP;

END;

To find all tables having column_name ABC, simple query as below should do.

select table_name 
     from dba_tab_columns 
     where column_name = UPPER('ABC');

PS: Metadata tables(dba_Tab_columns) stores column_name in upper case, to avoid any issues with case ,converting the case to upper for the literal.

Second query in PL/SQL block,

 SET SERVEROUTPUT ON 100000
    DECLARE 
    lv_count number(10):=0;
    l_str    varchar2 (1000);
    lv_col_name varchar2(255) :='ABC';

    BEGIN 
    FOR V1 IN 
    (select distinct table_name 
     from dba_tab_columns 
     where column_name = lv_col_name)

     LOOP
      dbms_output.put_line(lv_col_name||' '||v1.table_name);    
     END LOOP;

    END;
VN'sCorner
  • 1,532
  • 1
  • 9
  • 13
  • it should be - all_tab_columns i have also updated it and tried same error – Data2explore May 07 '20 at 17:28
  • It depends on what permission you have, i have replaced dba_tab_columns with all_Tab_columns, lv_query variable wasn't declare. It runs error free , could you try now. – VN'sCorner May 07 '20 at 17:35
  • @rakesh - Could you upvote if this worked for you. It should be pretty quick i believe. – VN'sCorner May 07 '20 at 17:37
  • @VN'Corner - i have one more question if i want to just search ABC ? – Data2explore May 07 '20 at 17:49
  • Just remove the BEGIN..END block within the Loop and replace it with dbms_output.put_line(v1.table_name); This will print all tables having column_name ABC.Alternately select distinct table_name from all_tab_columns where column_name = 'ABC' will return all tables with column name 'ABC' which is the simplest way of doing it. – VN'sCorner May 07 '20 at 17:52
  • @VN'Corner i have tried i am runinng into errors - can you pls post it ? – Data2explore May 07 '20 at 17:54
  • Thanks i know that query , but i wanted to run using the block query like reusable for future purpose... – Data2explore May 07 '20 at 18:03