To know tables' columns in a database

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. Received on Thu Feb 19 2004 - 17:54:55 CST