1

Possible Duplicate:
Oracle Search all tables all columns for string

I'm looking for a simple way to search in Oracle for a ID/Text in all tables. Any ideas?

I'm using currently SQL Developer. Thank you!

Community
  • 1
  • 1
Thomas
  • 8,357
  • 15
  • 45
  • 81

2 Answers2

1

You can use the USER_TABLES and USER_TAB_COLUMNS system tables to read what tables and columns you have.

Using that information, you can write a procedure or anonymous code block to dynamically build a query for searching those tables. Then execute it using EXECUTE IMMEDIATE.

But beware that this will probably not perform too well in large databases. Also, you might want to check the data type of the column to decide wether to include it in your search or not.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
1

You can execute this in sql-developer. (you might need to change %ID% to meet your naming convention)

SELECT 
    TABLE_NAME
    , COLUMN_NAME 
FROM 
    USER_TAB_COLUMNS 
WHERE 
    column_name like '%ID%'
Cos Callis
  • 5,051
  • 3
  • 30
  • 57
  • This is going to give him column names that match the ID, not tables/columns that have a value matching his search. – Tridus Jul 06 '11 at 14:51
  • @Tridus, this is going to give him the table names and the column names that are "like" '%ID%'. With that information OP can then search each table, in each id field for a value... which is how I read the question. – Cos Callis Jul 06 '11 at 14:58