searching for a certain word in all tables and columns in ORACLE

Question

I am wondering if there is anyway to search for a certain word or phrase in all tables/rows in SQL.

I have tried,

SELECT * 
FROM all_tab_cols
WHERE column_name LIKE '%WORD%'
;

However, this doesn't bring back what I want.

My desired output would be this query bringing back 'WORD' that is in all tables and all columns and all rows. I know its a very general search which may bring back a lot of data but I need to search for a certain word and have no idea which table/column this word is in.

Maybe something like this: https://community.oracle.com/message/14534461 — William Robertson, Jan 10 '18 at 14:58

score 0 · Answer 1 · answered Jan 10 '18 at 14:46

0

Try this in case you are searching for columns

SELECT * from dba_objects WHERE
object_name like '%WORD%'

answered Jan 10 '18 at 14:46

Bibin Mathew

455
3
11

This doesn't search an object, just a system view. – Ben Jan 10 '18 at 14:55
The question is about finding the contents of tables, i.e. dynamically querying every string column of every table. – William Robertson Jan 10 '18 at 14:55

searching for a certain word in all tables and columns in ORACLE

1 Answers1