Re: How to find all tables using a field value

On Dec 19, 1:41 pm, Homer <HomerS..._at_gmail.com> wrote:
> 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

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:37336026927381#42426083757635

The other alternative, if the column name is stable like "DEPARTMENT", is to simply write sql to generate a sql select, for each table in dba_tabl_cols that has the column. Find all the tables with the column, then look at all those tables for the value.

jg

--
@home.com is bogus.
http://crypto.com/paa.pdf