Re: Data Dictionary - Primary Keys

From: Jim Lyons <j.lyons_at_cc.utexas.edu>
Date: Mon, 21 Dec 1998 11:58:05 -0600
Message-ID: <367E8C2D.6E5B@cc.utexas.edu>

Ulrich F. Zeh wrote:
>
> hi Craig,
> if the tables are your's use USER_CONS_COLUMNS otherwise use
> ALL_CONS_COLUMNS
>
> select table_name, column_name
> from user_cons_columns
> order by position;
>
> Craig Fullerton wrote:
>
> > hi
> >
> > i am trying to get a list of the primary key columns in an Oracle 8.0.5
> > database, using the data dictionary. Any Ideas?
> >
> > i need the data returned as
> >
> > TableName PKColumnName

The problem with the above solution, I think, is that it returns all constraints, not just primary keys. Here's my (quick, seat-of-the-pants) solution:

select c.CONSTRAINT_NAME, c.TABLE_NAME, cc.COLUMN_NAME from dba_cons_columns cc, dba_constraints c where cc.constraint_name = c.constraint_name

      and cc.owner = c.owner
      and c.constraint_type = 'P'
      and c.owner = 'owneroftable' 
order by c.owner, c.table_name, c.constraint_name, 
         cc.position

I've included the constraint_name because I would like to see it but obviously you can take it out.

You can get a list of all owners by taking out the "and c.owner = 'owneroftable'" line and adding c.owner to the select list (unnecessary to get it to run but the output wouldn't make sense without it.)

--

Jim Lyons                     |  Operating Systems Specialist
ACITS                         |  512-475-9331

University of Texas at Austin | j.lyons_at_cc.utexas.edu http://uts.cc.utexas.edu/~jlyons Received on Mon Dec 21 1998 - 11:58:05 CST