" ALL_TAB_COLUMNS " is systable, has all the data types of table columns, etc.
" USER_TAB_COLUMNS " is systable, have all data types of table columns, etc. (which is the owner of the current user). (Thanks to Rene)
Example:
SET SERVEROUTPUT ON SIZE 100000 -- maybe you have a lot of table and columns DECLARE matches INTEGER; BEGIN FOR columns IN (SELECT table_name, column_name FROM user_tab_columns where data_type = 'VARCHAR2') LOOP EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||t.table_name||' WHERE instr('||t.column_name||' , :1) > 0' INTO matches USING 'What you search'; IF matches > 0 THEN dbms_output.put_line( t.table_name ||' '||t.column_name||' '||matches ); END IF; END LOOP; END; /
this query will output table_name '' column_name '' and count, if you have standard columns and table names, you can change the query as IF> 0 columns, then write the UNION query in a loop and return the cursor or return the table,