Re: To know tables' columns in a database

From: Hans Forbrich <hforbric_at_yahoo.net>
Date: Fri, 20 Feb 2004 05:06:26 GMT
Message-ID: <mBgZb.31786$D_5.4671@edtnps84>

Vince wrote:

>>> I would like to write a query which list key columns of my own tables.

>
>

>>> For each key column, I would like to get a row with
>>> - table name of this column
>>> - column name
>>> - key name ie constraint name
>>> - position with the key
>>> - a type = 'Primary', 'Secondary' or 'Foreign'
>>> - a target table only for foreign keys
>>> - a target column

>
>

>> You really want to look at Oracle's documentation at 
>> http://docs.oracle.com and search for 'TAB_COLUMNS', 'CONS_COLUMNS' 
>> and the other 100 views that support your requirement.

>
>
> I found interested views to find table name, column name, constraint
> name, and position within the key. Thanks Hans.
>
> But no views for:
> - a type = 'Primary', 'Secondary' or 'Foreign'
> - a target table only for foreign keys
> - a target column only for foreign keys
>
> Does anybody know a good better webpage with views on tables (mine was a
> short views list on oracle website, I tried all views I saw) or keywords
> to find what I am looking for?
>
> Thanks by advance.

Keep digging. There are dozens of dictionary views, some of which contain the information you want. The info you want is in the "Oracle9i Database Reference" documentation (or equivalent for the version you are using) - for 9iR2 it's document A96536. The section is about the definitions for constraints.

You really need to get familiar with these views if you want to do anything useful around Oracle in the long run.

/Hans Received on Thu Feb 19 2004 - 23:06:26 CST