Query On All Tables

Hi,

Our Oricle 9i database has several thousand tables in it. I want to create a query against all the tables in the db (table name unknown) to determine which table(s) contains the element N_REPORT_DT, for example, and display a list of those tables where N_REPORT_DT is a part of. Can this be done through SQL? How would the SQL be written? Thank you.

yes, it can be done, either by querying some INFORMATION_SCHEMA tables (assuming oracle supports these) or else by querying oracle's system catalog tables

unfortunately i don't know how, and this is the mysql forum

but i'm an admin, and i recommend that i move this question into the oracle forum, where i am sure you will find some knowledgeable oracle people

agreed?

Yes, please do. Thank you.

In Oracle LIKE operator is used to perform pattern matching, The pattern search character % is used to match any character and any number of characters. The pattern search character _ is used to match any single character. If you are looking for the actual % or _ in the pattern search, you can include an escape character in the search string and notify Oracle using the ESCAP clause. For example:

SELECT * from dba_objects
WHERE object_name like '%N\_REPORT\_DT%' ESCAPE '\'
/

Regards,

Thank you. Do I use a similar approach when I know the object name but do not know the table name where the object resides? I'm seaching for every table (17,000+ tables) that attempting to identify the ones that contains the object.

yes, you could.