Re: To know tables' columns in a database

From: Hans Forbrich <hforbric_at_yahoo.net>
Date: Fri, 20 Feb 2004 00:16:09 GMT
Message-ID: <dlcZb.26168$n17.17785@clgrps13>

Vince wrote:
> I use SQL/92 within a system where they are several users. I think that
> my question could be interesting for other people.
>
> 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
>
> For that, I can use a "select * from all_catalog where owner='VINCE'"
> giving:
>
> OWNER TABLE_NAME TABLE_TYPE
> VINCE PEOPLE TABLE
> etc
>
> Thanks by advance. I think it could help to see structure of tables, my
> tables and also those I import.

(Not sure where you are going with your mention of 'SQL92' - are you implying the SQL '92 standard & it's associated limitations?)

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.

/Hans Received on Thu Feb 19 2004 - 18:16:09 CST