RE: What columns are in the Primary Key

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 10 May 2001 11:17:20 -0700
Message-ID: <F001.002FFC29.20010510104551@fatcity.com>

> -----Original Message-----
> From: Helmut Daiminger [mailto:hdaiminger_at_vivonet.com]
>
> Is there a data dictionary view that gives me all the columns
> in a primary
> key of a table?

Try user_cons_columns.

It would probably be of interest to you to take a peek at the DBA_ views that are present in Oracle. See the Oracle Reference Manual. To get a list of those views, you can say

select view_name from dba_views where owner = 'SYS' ;

or to get a list of DBA_, ALL_, USER_ views you could do something like this select

   decode (substr (view_name, 1, 4),
           'USER', view_name,
           ' ' || view_name) as view_name
 from
   dba_views
 where
   owner = 'SYS' and
   (view_name like 'DBA\_%' escape '\'
    or view_name like 'ALL\_%' escape '\'
    or view_name like 'USER\_%' escape '\'
   )

order by
   substr (view_name, instr (view_name, '_') + 1),    substr (view_name, 1, instr (view_name, '_') - 1) ;

Typicall the DBA_ views show you information on all objects in the database, the ALL_ views show you information on all objects to which your logon user has access, and the USER_ views show you information on all objects owned by your logon user.

Once you're familiar with the DBA_ views, then you want to graduate to the V$ views. Finally, for dessert, you can look at X$ "tables", but only if you're good.

---

Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com Received on Thu May 10 2001 - 13:17:20 CDT