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;
/
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)
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 ;
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;
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;
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)