Global Scan

[Prabhu_7]

Is that possible to do a global scan on database and find
out which are the tables that has a particular string value.

Say "Investments".

I need to know which are the user tables has "Investments" as part of data ( In any of the records).

Raj

[Radim Jarosek]

Hi,

you can create simple procedure to go through all your tables to find a string value.

Radim

[A. Clay Stephenson]

Since you didn't bother to Identify your database, it's difficult to be too specific.

I'll get you started.

Phase 1)
Extract the table names and column names for any character columns. e.g. Oracle
SELECT TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE DATA_TYPE LIKE '%CHAR';

Phase 2)
Compare the extracted column names with your target string.

I would tend to do this in two SQL runs letting the Phase 2 select be built using the data from Phase 1.


In any event, this is going to be a slow process.