DEV Community

isabolic99
isabolic99

Posted on • Edited on

PL/SQL find me right table and column based on a value :)

#oracle #oraclecode #oracleplsql

Motive

How many times did I write someone an email, and ask them where is a specific value stored in a database (table, column) and the answer came in best scenario tomorrow. So I came with the idea to write my PL/SQL block to find me where is that particular value stored.

Solution

I wrote simple PL/SQL anonymous block to find me the necessary table and column for my task.

SET SERVEROUTPUT ON; DECLARE match_count INTEGER; --the owner/schema of the tables you are looking at v_owner VARCHAR2( 255) :='CUSTOMER' ; -- data type you look at (in CAPITAL) -- VARCHAR2, NUMBER, etc. v_data_type VARCHAR2( 255) :='VARCHAR2' ; --The value you are looking for with like "%" operator v_search_string VARCHAR2(4000) := '%TGP%' ; BEGIN FOR t IN (SELECT atc.table_name ,atc.column_name ,atc.owner FROM all_tab_cols atc WHERE atc.owner = v_owner AND data_type = v_data_type -- esclude vir. columns AND atc.column_id is not null -- exclude views AND not exists (select 1 from all_views where view_name = atc.table_name) ) LOOP EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || t.owner || '.' ||t. table_name|| ' WHERE UPPER("'||t.column_name ||'") LIKE UPPER(:1)' INTO match_count USING v_search_string ; IF match_count > 0 THEN dbms_output.put_line( t. table_name ||' ' ||t.column_name ||' '||match_count ); END IF; END LOOP; END; /
Enter fullscreen mode Exit fullscreen mode

parameters

The code has 3 variables:

  • v_owner - the schema where query will look for
  • v_data_type - data type
  • v_search_string - The value you are looking for with like "%" operator

query

Code executes FOR LOOP on a query to find table_name, column_name and table owner(schema).
The query uses upper parameters v_owner and v_data_type, also exclude virtual columns and views.

SELECT atc.table_name ,atc.column_name ,atc.owner FROM all_tab_cols atc WHERE atc.owner = v_owner AND data_type = v_data_type -- exclude vir. columns AND atc.column_id is not null -- exclude views AND not exists (select 1 from all_views where view_name = atc.table_name)
Enter fullscreen mode Exit fullscreen mode

EXECUTE IMMEDIATE 

 EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || t.owner || '.' ||t. table_name|| ' WHERE UPPER("'||t.column_name ||'") LIKE UPPER(:1)' INTO match_count USING v_search_string ;
Enter fullscreen mode Exit fullscreen mode

Inside LOOP the code runs EXECUTE IMMEDIATE statement with concatenating table owner, table name, and column and parameter v_search_string. So for every column inside every table, it runs to check if there is close enough value to that I look for.

Output

If the is a match (> 1) output table_name, column_name and match count to dbms_output.

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

Also since dbms_output has to be turned on and has a limit, I set at the beginning of PL/SQL block

SET SERVEROUTPUT ON;
Enter fullscreen mode Exit fullscreen mode

which means the ARGUMENT/VALUES that I will be passing inside dbms_output.put_line prints the argument on the main console (Script output).

Top comments (0)