Re: table relationships

Ed Stevens <nospam_at_noway.nohow> wrote in message news:<ujoji0t6cnpbn4kfita4u5rf5re779g0ct_at_4ax.com>...
> On Sun, 22 Aug 2004 13:40:14 -0700, "Jeff" <idont_at_thinkso.com> wrote:
>
> >"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> >news:1093194494.325919_at_yasure...
> >>
> >> Query the data dictionary views. Run this query for a list of them.
> >>
> >> SELECT view_name
> >> FROM all_views
> >> WHERE view_name LIKE 'ALL%';
> >>
> >> But why connect to Oracle with MS Access? An overwhelming desire to use
> >> the worst possible tool to do the job?
> >
> >HAHAHAHA. No, us low ranking po' folk in the military don't have Oracle
> >installed let alone get Oracle training, or ANY database training for that
> >matter. All government desktop PCs come with Microsoft Access installed and
> >I had to teach myself to use it. I have a couple of stand-alone
> >applications I have to use that connect to an Oracle database, but the
> >applications leave ALOT to be desired because I still have to build my own
> >lists of data and compile statistics by hand over and over. If I can
> >connect to the Oracle database with Access and ADO, I can build my own
> >queries and reports once and be done with it at the click of a button
> >whenever it's needed. Whether or not Access and ADO are the worst possible
> >tools, it's still a hell of alot faster than doing it by hand the way I do
> >it now. The Oracle database I tried to connect to turned out to have about
> >2000 tables with a jillion fields. I need to figure out what the
> >relationships are before I can build my own queries.
> >
> >Jeff
> >
>
> Ah
>
> There's nothing in Oracle, or any other dbms or pseudo-dbms that will
> necessarily give you that information. The closest thing you'll come
> to is examining the foreign-key definitions -- if they exist.
> Packaged apps are notorious for NOT defining them, preferring to
> enforce RI in the app instead of the database. You can query
> ALL_TABLES to get a list of tables, ALL_TAB_COLUMNS to get a list of
> columns, and ALL_TAB_COMMENTS and ALL_COL_COMMENTS (if they have been
> populated, and that's a big 'IF') to get table and column
> descriptions. ALL_DEPENDENCIES might (or might not) reveal anything
> of interest.
>

If foreign keys are defined, ALL_CONSTRAINTS will give you all information you need. Not an easy view to navigate though... Check description in Oracle Reference book (Part No A96536-02 for 9iR2).

> Packaged apps are notorious for simply defining tables and columns,
> and those with cyptic names. No RI, no comments. The only knowledge
> of the data itself is in the app, not the DB. When this is the case
> there is there is nothing within the database to indicated that table
> MCOMITMAS is a child of table MCOMOTMAS nor that they are related by
> columns MCOMITMAS.COSQFL and MCOMOTMAS.CISQGL. Nothing to indicate
> that column SBEMFU.SBEMCJ, which is defined as a NUMBER is actually
> used to store dates . . . .
>
> I'm afraid your best bet to the knowledge you seek is documentation
> from the app vendor. Such documentation may or may not be made
> available. Vendors tend to see such information as trade secret.
Received on Tue Aug 24 2004 - 11:07:37 CDT