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
I took Jeff's idea and changed USER_ to ALL_. It wouldn't work with
USER_ but ALL_ does the job. Thanks Jeff.
Received on Thu Dec 20 2007 - 12:49:59 CST
