Re: table relationships

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.

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 Mon Aug 23 2004 - 07:48:10 CDT