Re: How to find all tables using a field value

On Dec 19, 12:18 pm, jefftyzzer <jefftyz..._at_sbcglobal.net> wrote:
> On Dec 19, 11:45 am, Homer <HomerS..._at_gmail.com> wrote:
>
> > Hi,
>
> > I need a way to find all the tables whose field contains a certain
> > value. The problem I'm encountering is the vendor software uses
> > oracle database and it has countless number of tables (around 1,000).
> > To top it off, the table name does not match the front-end. I'm
> > trying to find all the tables that has 'Finance' as a department
> > field. Can this be done?
>
> > Thank you,
> > Jon
>
> Jon,
>
> To clarify: are you looking for "Finance" as (part of) the name of a
> column, or rather as the value or part of the value of any one of the
> columns in any one of the ca. 1,000 tables?
>
> If the latter, I've seen recommendations for this in other DB
> newsgroups that involve dumping the entire database in ASCII-readable
> format and grep-ing (or some such) through the dump.
>
> If it's field names you're looking at, something like:
>
> SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE UPPER(COLUMN_NAME) LIKE
> '%FRANCE%';
>
> should work.
>
> (If it's guaranteed that all entries in this column are already in
> upper case then you can, of course, dispense with the call to the
> UPPER function).
>
> HTH,
>
> --Jeff

Hi Ed and Jeff,

Finance is a value of a column rather than the column name. The problem that is that there's no rhyme or reason to the table naming convention and it doesn't help when there are tons of tables in the database.

Thanks again. Received on Wed Dec 19 2007 - 15:41:26 CST