Re: To know tables' columns in a database

From: Hans Forbrich <hforbric_at_yahoo.net>
Date: Fri, 20 Feb 2004 16:30:34 GMT
Message-ID: <KCqZb.28153$n17.9268@clgrps13>

Vince wrote:
> I recall what I try to get:
>
> 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 within the key
> - a type = 'Primary', 'Secondary' or 'Foreign'
> - a target table only for foreign keys
> - a target column
>

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

>
>
> Now, I have a select query which give me something as:
>
> TABLE_NAME COLUMN_NAME CONSTRAINT_NAME TYPE
> TABLE1 COL1 SYS_C00208068 Primary
> TABLE1 COL2 SYS_C00208067 Foreign
> etc
>
> I try to make:
>
> SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, TYPE,
>
> /* but also two new columns
>
> 1st) if type = 'Foreign' then take a value (not a string as 'xxxx') in a
> certain table
> 2nd) if type = 'Foreign' then take another value in a certain table
>
> */
>
> FROM
> ( SELECT ... /* what give me the previous table */ ) <---- ALPHA
>
> I try to use DECODE and CASE ...
>
> In fact, the main problem is that I cannot use twice "type column" which
> is extracted from the the ALPHA select. The error if a use twice same
> column is "SQL error: Il n'y a plus de données à lire dans le socket"
> <-> There is any data to read in socket.
>
> Any idea? Thanks by advance.

1. You need to learn about JOIN and TABLE ALIAS.
2. Look at ALL_CONSTRAINTS as well as ALL_CONS_COLUMNS
3. For the <optional> Foreign Key case, you need to learn Outer Join
4. When discussing messages, give the error number so we can see the official translation.

Start with:

SELECT This.constraint_name, This.constrainttype,

        This.table_name, That.table_name    FROM all_constraints This, all_cons_columns Got,

        all_constraints That
  WHERE This.constraint_name = Got.constraint_name     AND This.r_constraint_name = That.constraint_name (+)

I hope you post the rest, including an explanation about how/why it works. There are many students who ask this on a regular basis - put the answer into the archives and tey will likely appreciate it (if tey bother to search).

/Hans Received on Fri Feb 20 2004 - 10:30:34 CST