how get all table name that contains a value

Question

I need to get all table name that contains a value like :"AIDA". I don't newbie and I don't know how I can do this. Anyone cna help me?

Answer 1

This query gives you all the tables with a column name containing 'AIDA' :

SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name like '%AIDA%';

To search data inside of columns it is a little bit more comple, you can use this procedure:

create or replace procedure search_schema( p_string in varchar2 )
authid current_user
as
l_cols long;
l_where long;
l_cursor sys_refcursor;
l_cname varchar2(4000);
begin
dbms_application_info.set_client_info( '%' || p_string || '%' );
for x in ( select table_name from user_tables )
loop
l_cols := 'case when 1=0 then ''x'' ';
l_where := ' where ( 1=0 ';
for y in ( select '"' || column_name || '"' column_name
from user_tab_columns
where table_name = upper(x.table_name)
and (data_type in ( 'CHAR', 'DATE', 'FLOAT', 'NCHAR', 'NUMBER', 'NVARCHAR2', 'VARCHAR2' )
or data_type like 'INTERVAL%' or data_type like 'TIMESTAMP%' )
)
loop
l_cols := l_cols || ' when ' || y.column_name ||
' like sys_context(''userenv'',''client_info'') then ' ||
' ''' || y.column_name || '''';
l_where := l_where || ' or ' || y.column_name || ' like sys_context(''userenv'',''client_info'') ';
end loop;
open l_cursor for 'select ' || l_cols || 'else null end cname from ' ||
x.table_name || l_where || ') and rownum=1';
fetch l_cursor into l_cname;
if ( l_cursor%found )
then
dbms_output.put_line( x.table_name || ' ' || l_cname );
end if;
close l_cursor;
end loop;
end;
/

and use it like that:

SQL> set serveroutput on size 1000000
SQL> exec search_schema( 'AIDA' )

source: AskTom

Answer 2

As you're looking for a string (which "AIDA", obviously, is), then have a look at the following simple procedure.

First, let's prepare the playground: my Scott schema is relatively poor, it contains a few tables and I'm pretty much sure none of them contains AIDA in any column.

SQL> INSERT INTO test (dname) values ('AIDA');

1 row created.
    
SQL> UPDATE emp SET ename = CASE WHEN deptno = 10 THEN 'SERAIDAS'   -- contains AIDA as a SUBSTRING
  2                              WHEN deptno = 20 THEN 'SAIDA'      -- also contains AIDA as a substring
  3                              ELSE ename
  4                         END;

14 rows updated.


SQL>

Let's test it:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    l_str VARCHAR2(500);
  3    l_cnt NUMBER := 0;
  4  BEGIN
  5    FOR cur_r IN (SELECT u.table_name, u.column_name
  6                  FROM user_tab_columns u
  7                  WHERE data_type like '%CHAR%'
  8                 )
  9    LOOP
 10      l_str := 'SELECT COUNT(*) FROM ' || cur_r.table_name ||
 11                ' WHERE ' || cur_r.column_name || ' like (''%AIDA%'')';
 12
 13      EXECUTE IMMEDIATE (l_str) INTO l_cnt;
 14
 15      IF l_cnt > 0 THEN
 16        dbms_output.put_line(l_cnt ||' : ' || cur_r.table_name);
 17      END IF;
 18    END LOOP;
 19  END;
 20  /
8 : EMP
1 : TEST

PL/SQL procedure successfully completed.

SQL>

The result says that there are two tables: EMP and TEST that contain at least 1 row with AIDA somewhere in their %CHAR% (so, CHAR, VARCHAR, VARCHAR2) datatype columns. EMP has 8 rows, TEST just 1.

As you asked

I need to get all table name that contains a value like :"AIDA".

I presume that that's it. More or less.