Skip to content

Instantly share code, notes, and snippets.

@hewerthomn
Last active August 13, 2020 16:43
Show Gist options
  • Save hewerthomn/2911178d358ce1436e2a29c371678eea to your computer and use it in GitHub Desktop.
Save hewerthomn/2911178d358ce1436e2a29c371678eea to your computer and use it in GitHub Desktop.
Search for a string across all table in Oracle
-- Source of inspiration of this snippet
-- https://www.experts-exchange.com/questions/27396707/Search-for-a-string-across-all-tables-in-oracle.html#a42581782
declare
num_rows number;
sql_text varchar2(2500);
sql_info varchar2(1000);
v_text varchar2(100) := '''%value_in_lowercase%''';
begin
dbms_output.enable(1000000);
for x in (select table_name, column_name,OWNER, data_type, data_length from ALL_tab_columns
where data_type in ('VARCHAR','VARCHAR2')
and column_name IS NOT NULL
-- put your rules here to limit tables
and table_name NOT LIKE 'TMP_%'
-- and table_name NOT IN ('SIS_CONF')
and owner = 'SYSTEM' -- change to your schema owner name
order by owner,table_name,column_name)
loop
sql_text:='SELECT COUNT(*) INTO :num_rows FROM '||X.OWNER||'.'||x.table_name||' WHERE LOWER('||x.column_name||') LIKE '||v_text|| ' -- ' || x.data_type || ' ' || x.data_length;
-- dbms_output.put_line (sql_text); -- uncomment this line to debug and discover what table to add in filters
execute immediate sql_text into num_rows;
if num_rows>0
then
sql_info:='SELECT X.'||X.COLUMN_NAME||', X.* FROM '||X.OWNER||'.'||x.table_name||' X ' ||
'WHERE LOWER('||x.column_name||') LIKE '||v_text ||' ORDER BY X.'||X.COLUMN_NAME||'; -- '||X.COLUMN_NAME;
dbms_output.put_line (sql_info);
end if;
end loop;
end;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment