Geek Answers Handbook

I want to find a specific value in all columns of all tables in oracle 11g

Is it possible to search every field of each table for a specific value in Oracle.

I want to do this without any procedure.

Can we do this with a request?

+6
search oracle11g
source share
1 answer

You can do this with a single request, although it is a bit confusing. This query will look for all CHAR and VARCHAR2 columns in the current schema for the string 'JONES'

 select table_name, column_name from( select table_name, column_name, to_number( extractvalue( xmltype( dbms_xmlgen.getxml( 'select count(*) c from ' || table_name || ' where to_char(' || column_name || ') = ''JONES''' ) ), 'ROWSET/ROW/C' ) ) cnt from (select utc.*, rownum from user_tab_columns utc where data_type in ('CHAR', 'VARCHAR2') ) ) where cnt >= 0

Note that this is an adapted version of the Laurent Schneider query to count the rows in each table with a single query.

+12
source share

More articles:


All Articles