-1

I am trying to find data in a database with hundreds of tables. I am currently "select staring" each table to view their contents, and it is taking forever! Is there a way that I can write a query to filter all of the tables with specific contents? For example, suppose I wanted to find all of the tables that contained "Montana" in any of the columns. Is that possible?

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
325
  • 594
  • 8
  • 21

1 Answers1

2

If you loop through all "character"-like columns, you'd do it like this (it also calculates how many times the search string is found in that table/column). I don't have any Virginia, so I'm searching for KING instead.

SQL> set serveroutput on
SQL> declare
  2    l_cnt number;
  3  begin
  4    for cur_r in (select table_name, column_name
  5                  from user_tab_columns
  6                  where data_type like '%CHAR%'
  7                 )
  8    loop
  9      execute immediate 'select count(*) from ' || cur_r.table_name ||
 10                        '  where ' || cur_r.column_name ||' = ' ||
 11                        chr(39) || 'KING' || chr(39)
 12                   into l_cnt;
 13      if l_cnt > 0 then
 14         dbms_output.put_line(cur_r.table_name ||'.'|| cur_r.column_name ||': '|| l_cnt);
 15      end if;
 16    end loop;
 17  end;
 18  /
EMP.ENAME: 1
V_EMP.ENAME: 1

PL/SQL procedure successfully completed.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • ```set serveroutput on declare l_cnt number; begin for cur_r in (select table_name, column_name from user_tab_columns where data_type like '%CHAR%' ) loop execute immediate 'select count(*) from ' || cur_r.table_name ||' where ' || cur_r.column_name ||' = ' || chr(39) || 'KING' || chr(39) into l_cnt; if l_cnt > 0 then dbms_output.put_line(cur_r.table_name ||'.'|| cur_r.column_name ||': '|| l_cnt); end if; end loop; end;``` – 325 Oct 06 '21 at 19:05
  • I get an error "missing or invalid operation" when I try to save the procedure – 325 Oct 06 '21 at 19:05
  • Save the procedure? How? Where? As you can see, code I posted *works*; I don't know what you're doing. – Littlefoot Oct 06 '21 at 19:09
  • what is dbms_output ? That line is where my error is occurring – 325 Oct 06 '21 at 19:16
  • It is dbms_output (underline, not dot). It is Oracle's built-in package; its procedure put_line is used to display output on the screen (just like in my example). Which database do you use? – Littlefoot Oct 06 '21 at 19:21
  • I'm not sure. I am new to this. I am using Oracle SQL Developer if that answers your question. The dot was a typo when I was typing here; I have it typed correctly in my script. – 325 Oct 06 '21 at 19:23
  • Oracle SQL Developer is a tool. Most often it is used along with an Oracle database. You should really know which database you use ... Could you post a screenshot which illustrates your problem? – Littlefoot Oct 06 '21 at 19:25
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/237905/discussion-between-325-and-littlefoot). – 325 Oct 06 '21 at 19:26
  • I opened a chat to show a screenshot – 325 Oct 06 '21 at 19:33
  • At the end, it turned out that @325 ran that code in a *project*, instead of a SQL worksheet window. It is an Oracle database (error 325 got was ORA-00922). – Littlefoot Oct 06 '21 at 20:34