Skip to content

Instantly share code, notes, and snippets.

@claudiuschan

claudiuschan/gist:9eae455622e8a0a2a336f73b95504e9b

Created May 29, 2019 03:45
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save claudiuschan/9eae455622e8a0a2a336f73b95504e9b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save claudiuschan/9eae455622e8a0a2a336f73b95504e9b to your computer and use it in GitHub Desktop.
Download ZIP
Search Oracle All DB Table Colum for a value
Raw
gistfile1.txt
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 '1342177291';
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment