select table name from all tables where column has specific value

i need to select table names from all tables where column has specific value something like:

select table_name from all_tab_columns where column_name = 'VALUE'; 

but with value condition thanks

Add Comment
2 Answer(s)

What you want is to search all columns in the database with some values say "ABC" and list of such tables.

For this you have to write a dynamic script to pick one table at a table and search all columns in that table and loop through it.

Add Comment

Well, it is not clear what are you asking for, whether is the column name of a table or the actual value of the column itself.

I guess it is the second, as the first one you already know. Oracle does not store column values in any dictionary view, because it is non sense. Values are stored in the corresponding tables.

So , a way to do that could be using PL/SQL, but it will take a long time if your database is big. In my example, I am looking for a string which I can limit by only applying to columns where the data type is varchar2 or char, and as I am looking for a specific value, I discard all columns which its length is lower than the value I am looking for.

set serveroutput on size unlimited echo on verify off timing on  declare vowner varchar2(128); vtable varchar2(128); vcolum varchar2(128); vvalue varchar2(40) := 'TEST_TABLE'; vcount pls_integer; begin for c in ( select owner , table_name from all_tables order by 1 , 2 ) loop      vowner := c.owner;     vtable := c.table_name ;     for h in ( select column_name from all_tab_columns where owner = vowner and table_name = vtable                 and data_type in ( 'CHAR' ,  'VARCHAR2' ) and data_length >= length(vvalue)                 order by column_id )     loop         vcolum := h.column_name;         execute immediate ' select count(*) from '||vowner||'.'||vtable||' where upper('||vcolum||') = '''||vvalue||''' ' into vcount ;         if vcount > 0         then              dbms_output.put_line('Found '||vvalue||' in table --> '||vowner||'.'||vtable||' | column --> '||vcolum||' ');         end if;     end loop; end loop; end; / 

An example where I limited the tables to look for

SQL> set serveroutput on size unlimited echo on verify off timing on lines 200 declare vowner varchar2(128); vtable varchar2(128); vcolum varchar2(128); vvalue varchar2(40) := 'TEST_TABLE'; vcount pls_integer; begin for c in ( select owner , table_name from all_tables where owner = 'TEST_PERF' order by 1 , 2 ) loop         vowner := c.owner;         vtable := c.table_name ;         for h in ( select column_name from all_tab_columns where owner = vowner and table_name = vtable and data_type in ( 'CHAR' ,  'VARCHAR2' ) and data_length >= length(vvalue)  order by column_id )         loop                 vcolum := h.column_name;                 execute immediate ' select count(*) from '||vowner||'.'||vtable||' where upper('||vcolum||') = '''||vvalue||''' ' into vcount ;                 if vcount > 0                 then                       dbms_output.put_line('Found '||vvalue||' in table --> '||vowner||'.'||vtable||' | column --> '||vcolum||' ');                 end if;       end loop; end loop; end; /  Found TEST_TABLE in table --> TEST_PERF.T | column --> C1 Found TEST_TABLE in table --> TEST_PERF.TEST_OBJECTS | column --> OBJECT_NAME  PL/SQL procedure successfully completed.  Elapsed: 00:00:17.29 SQL> 
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.