Link to home
Start Free TrialLog in
Avatar of Sam OZ
Sam OZFlag for Australia

asked on

Oracle query to search all non system tables for a given value

Looking for a query to query all non-system tables in an Oracle 11g database for a given value
Additionally, If you can given a flavour of the same query which can search all the table starting with "ENV"  or "DOC"  , it is much appreciated
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

This has been asked several times here.

It will be a SLOW query but you can use a DBMS_XMLGEN.getxmltype trick to do it.  An example is here:
https://www.experts-exchange.com/questions/28069987/identifying-columns-in-all-the-tables-with-a-specific-value.html?anchorAnswerId=39001854#a39001854

You can tweak the where clause for specific schemas (not use NOT IN to remove certain ones.

Adding your table requirement something like:
WHERE owner in ('SCHEMA1','SCHEMA2)
   AND table_name LIKE 'ENV%'  or table_name LIKE 'DOC%
Avatar of Sam OZ

ASKER

May be silly, but what is the equivalent if the column is string ( I am looking for a value 'MyDoc'
   'select count(*) X from ' || table_name || ' where ' || column_name || ' = -1'
               ),
Avatar of Sam OZ

ASKER

Also, Not sure what is wrong ( I have not worked oracle procedures) . I get an error as in snapshot when trying to run the query
User generated image
This part of your query causes the "invalid number" error:

...where ' ||column_name || '  = 189507'

You could try changing that to this:

...where ' ||column_name || '  = ''189507'''

(Those are two single quotes after the "=" sign and three single quotes at the end.)

But that would make sense only if the column_name you want to search in is actually '189507'.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial