Sam OZ
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
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
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'
),
'select count(*) X from ' || table_name || ' where ' || column_name || ' = -1'
),
ASKER
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'.
...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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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%