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?
CodePudding user response:
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>