Oracle-L: columns with primary key constraint

From: Steven Haas <steven.haas_at_snet.net>
Date: Mon, 30 Dec 2002 05:59:13 -0800
Message-ID: <F001.00524220.20021230055913@fatcity.com>

Good Morning List,

I am trying to build a query to display the following for any table

column_name is_pk

----------- -----
COLUMN_1    (PK-1)
COLUMN_2    (PK-2)
COLUMN_3
COLUMN_4

COLUMN_5
COLUMN_6 I can get the two columns with (PK) with...

select utc.column_name
,decode(ucc.column_name,null,null,'
(PK-'||ucc.position||')') is_pk

from   user_tab_columns utc,
       user_cons_columns ucc,
       user_constraints uc

where utc.table_name = 'TAB_A'
and uc.constraint_type = 'P'
and utc.table_name = uc.table_name
and uc.constraint_name (+) =
ucc.constraint_name
and utc.column_name (+) = ucc.column_name order by utc.column_id

but can't seem to get the columns not part of the PK. I suspect I am missing an outer-join somewhere, but can't seem to figure it out.

If possible I would like to put an "(FK)" next to columns that have a FK constraint as well.

Thanks.

Steve Haas
Opus Consultants, LLC
860.408.1512 (office/fax)
860.651.9475 (home)
steven_haas_at_opus-consultants.com
steven.haas_at_snet.net

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Steven Haas
  INET: steven.haas_at_snet.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

Received on Mon Dec 30 2002 - 07:59:13 CST