Oracle database search all tables for string, return row data

Question

I need to search all tables in an oracle database for a specific string and return all the records where that string is found. I am working with SQL Developer. There are several really useful scripts and stored procedures that have been posted online that provide a way to search the entire database and they all seem to be able to return the table name and the column name where the string is found. I actually want to see the rows like a select * statement but I would like it to be for all the tables in my database. I want to note that I am very much a beginner and I'm not familiar with advanced PL/SQL scripting and database concepts so though I've tried and tried I can't seem to figure it out so any input would be appreciated.

Here is a script that I was trying to modify (someone else's solution)

declare
 l_count number := 0;
 l_str varchar2(20) := '%test%';
begin
 for rec in (select table_name, column_name
             from user_tab_columns
             where data_type in ( 'VARCHAR2' , 'VARCHAR', 'CHAR' ) ) loop
  execute immediate 'select count(*) from '||rec.table_name||
                ' where '||rec.column_name||' like '''||l_str||'''' into l_count;
  if l_count > 0 then
   dbms_output.put_line('Table : '||rec.table_name||'  '||rec.column_name);
  end if;
 end loop;
end;
/

what data do you want this to return exactly? (this soltn posted returns uses counts to determine if the table contains any rows with that string) — tbone, Oct 16 '12 at 19:37
I want to be able to obtain the records that have the particular string and also return all the column values for those records (the information for the whole row). For example when select * from emp; all the records with all the columns and values are returned. So I want something like that but for the whole database and to be able to filter it down to the records with the particular searched string. I apologize if this is vague. — user1750939, Oct 16 '12 at 20:38
You're not going to be able to do a `SELECT *` on all tables where there is a match with your search term, because the columns in each of the tables will almost certainly be different. You need to determine what output columns you really need, then join all the tables together to get those columns, then filter based on your search term — Tobsey, Oct 16 '12 at 20:39
dare I ask what you need this for? The answer would probably dictate a better approach than trying to dump select * output from (possibly) every table in your db. If its homework, it seems an exercise in bad design (or problem solving), but again I would need to know the real problem that is trying to be solved by this. — tbone, Oct 17 '12 at 12:23
Thanks for the responses. I apologize if I haven't explained myself clearly. It's not homework. I'm creating a database that contains patient information (and related data). I need to search a specific keyword and retrieve all the records and be able to view the related information for each patient. There are several different tables with different column names and I don't know which tables and which columns that keyword might be found. — user1750939, Oct 17 '12 at 19:19

Luke Liu · Accepted Answer · 2012-10-17T19:36:52.207

Are you trying to build a full database search based on a key word? You can get table names , table column names and row counts by following code. But you cannot get row data within same code, you can get row data based on search results.

   --Set output size
   SET serveroutput ON size 1000000

   DECLARE
      -- var table Name for cursor loop.
      t_c1_tname      user_tab_columns.table_name%TYPE;
      -- var column name for dynamic sql statement.
      t_c1_cname      user_tab_columns.column_name%TYPE;
      -- var string for dynamic sql statement.
      t_command       VARCHAR2(200);
      -- var for your search key word.
      l_str varchar2(20) := '%test%';
      -- var for dynamic cursor.
      t_cid           INTEGER;
      -- var for total row counts.
      t_total_records NUMBER(10);
      -- var for stat of executing dynamic sql statement.
      stat            INTEGER;
      --var for each loop row counts.
      row_count       INTEGER;
      -- var for minimum search result, here I set value = 0;
      t_limit         INTEGER := 0;    -- Only show tables with more rows

      -- cursor gets all table name, column name.
      CURSOR c1 IS select table_name, column_name
        from user_tab_columns
        where data_type in ( 'VARCHAR2' , 'VARCHAR', 'CHAR' );
    BEGIN
      t_limit := 0;
      OPEN c1;
      LOOP
      FETCH c1 INTO t_c1_tname,t_c1_cname;
      EXIT WHEN c1%NOTFOUND;
      -- Here create dynamic sql statement. 
      t_command := 'SELECT COUNT(0) FROM '||t_c1_tname || ' where ' || t_c1_cname ||' like '''|| l_str||'''';
      t_cid := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(t_cid,t_command,DBMS_SQL.native);
      DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
      -- Here execute dynamic sql statement. 
      stat := DBMS_SQL.EXECUTE(t_cid);
      row_count := DBMS_SQL.FETCH_ROWS(t_cid);
      -- Here get total row counts for each loop.
      DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
      IF t_total_records > t_limit THEN
         --Here output results
         DBMS_OUTPUT.PUT_LINE(RPAD(t_c1_tname,55,' ')||RPAD(t_c1_cname,55,' ')||
                        TO_CHAR(t_total_records,'99999999')||' record(s)');
         -- here you can insert results into your table.
         --INSERT INTO search_db_results VALUES (t_c1_tname,t_c1_cname,t_total_records);
      END IF;
      DBMS_SQL.CLOSE_CURSOR(t_cid);
      END LOOP;
      CLOSE c1;

     -- COMMIT if you have any insert statement.
     -- COMMIT;
    END;
    /

Thanks, I was referring to a full database search based on a key word. You seem to be correct that this cannot be done within the same code as I've come to realize. I am able to get the table name and row name. Is there a way to store the results in a table or an array then use it to retrieve those rows? I've tried creating an empty table and tried to insert my results into it using "execute immediate 'insert into search_db_results..." while within the loop but was not able to insert successfully. Again, my knowledge level is very basic so if you could be descriptive that would really help — user1750939, Oct 17 '12 at 19:09
Change "DBMS_OUTPUT.PUT_LINE..." code to "INSERT INTO search_db_results VALUES (t_c1_tname,t_c1_cname,t_total_records); ". — Luke Liu, Oct 17 '12 at 19:18

Oracle database search all tables for string, return row data

1 Answers1

Linked

Related