3

I have a Oracle database (11g2). But i don't know how to find a text in all of table on a database. Because there are so many tables, can't find down by one column, one table... it takes me a lot of times. Is there any way to do this ? Or any tools, software can help me to do this ? Please help me! Thank in advance.

1
  • 1
    stackoverflow.com/questions/208493/…; but your real problem is apparent lack of documentation. Your time would be better spend documenting your schema than going on wild goose chases that might turn up nothing.
    – Mat
    Dec 9, 2012 at 14:04

1 Answer 1

1

See https://lalitkumarb.wordpress.com/2015/01/06/sql-to-search-for-a-value-in-all-columns-of-all-atbles-in-an-entire-schema. In short (with my slightly modified version of the code):

SELECT DISTINCT table_name, column_name
  FROM user_tab_cols,
  TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
  || column_name
  || ' from '
  || table_name
  || ' where upper('
  || column_name
  || ') like upper(''%'
  || '&searchword'
  || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
  where table_name not in (
    select distinct table_name
      from user_tab_cols where data_type like 'SDO%'
      or data_type like '%LOB')
  order by table_name, column_name;

I exclude tables that contain LOB or spatial columns as they can fail. Add other filters as you please. This will be very expensive to run in a large database!

EDIT: &searchword is the text you are searching for, of course.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.