-1

Is there SQL to Search for a particular VALUE in all COLUMNS of all TABLES in an entire SCHEMA in Oracle DB. Please explain.

Sivagami Annadurai
  • 1
  • There are no magic functions to do this, you need to write code which searches in every column. – Tim Biegeleisen Jun 28 '22 at 06:01
  • A database is not a text file, so there's no out-of-the-box Ctrl+F functionality for a specific value in *every table*. It is not what should be performed, especially on hundred gigabytes tables – astentx Jun 28 '22 at 06:38

1 Answers1

0

You'll need dynamic SQL, as you have to compose select statement.

Here's an example; adjust it according to your needs because datatypes matter (e.g. you can't compare numbers to dates and such; I'm handling it simply by skipping over errors):

SQL> declare
  2    l_search_value varchar2(20) := '10';
  3    l_str varchar2(500);
  4    l_cnt number;
  5  begin
  6    for cur_r in (select table_name, column_name, data_type
  7                  from user_tab_columns
  8                 )
  9    loop
 10      begin
 11        l_str := 'select count(*) from ' || cur_r.table_name ||
 12                 '  where ' || cur_r.column_name || ' = ' || l_search_value;
 13        execute immediate l_str into l_cnt;
 14
 15        if l_cnt > 0 then
 16           dbms_output.put_line(rpad(cur_r.table_name ||'.'||cur_r.column_name, 30, ' ') ||
 17                                  ' found ' || l_cnt || ' time(s)');
 18        end if;
 19
 20      exception
 21        when others then
 22          --dbms_output.put_line(rpad(cur_r.table_name ||'.'||cur_r.column_name, 30, ' ')
 23          --                       ||' - ' || cur_r.data_type ||'; '|| sqlerrm);
 24          null;
 25      end;
 26    end loop;
 27  end;
 28  /
DEPARTMENTS.DEPARTMENT_ID      found 1 time(s)
DEPT.DEPTNO                    found 1 time(s)
EMP.DEPTNO                     found 3 time(s)
EMPLOYEES.DEPARTMENT_ID        found 3 time(s)

PL/SQL procedure successfully completed.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • PL/SQL procedure successfully completed. How do i retrieve the result? – Sivagami Annadurai Jun 28 '22 at 07:27
  • how do i list the output column and table names for that string after running the script? – Sivagami Annadurai Jun 28 '22 at 07:29
  • I guess you didn't SET SERVEROUTPUT ON. – Littlefoot Jun 28 '22 at 07:38
  • This piece of code doesn't require any special privileges. You, as owner, have right to read USER_TAB_COLUMNS and select from any table you own. Therefore, did you run EXACTLY this code, or did you modify something? – Littlefoot Jun 28 '22 at 08:00
  • i am running the script with set serveroutput on. Still not getting the result. is there anything i am missing? SET SERVEROUTPUT ON SIZE 100000 – Sivagami Annadurai Jun 28 '22 at 08:05
  • In that case, string you're searching for wasn't found in any table. – Littlefoot Jun 28 '22 at 08:06
  • ok. Just confirming. I should change for search string instead of '30' in the script right? and this script can search varchar and numeric as well? if l_cnt > 0 then 16 dbms_output.put_line(rpad(cur_r.table_name ||'.'||cur_r.column_name, 30, ' ') || 17 ' found ' || l_cnt || ' time(s)'); – Sivagami Annadurai Jun 28 '22 at 08:17
  • Nope; 30 (line #16) is here to *nicely* format output, it belongs to the RPAD function. Search string's value is in line #2. – Littlefoot Jun 28 '22 at 08:21