RE: columns with primary key constraint

Charu,

This is exactly what I was looking for.

I will try to modify this now to include an "(FK)" for any column with a foreign key constraint.

Thanks for the extra pair of eyes.

Happy New Year all!

Steve

• Charu Joshi <joshic_at_mahindrabt.com> wrote:
  > Hi Steve,
  >
  > Are you looking for something like this?:
  >
  > SELECT utc.table_name, utc.column_name
  > ,DECODE(NVL(ucct.cln, ' '), ' ',' ',
  > '(PK-'||ucct.pos||')') is_pk
  > FROM user_tab_columns utc,
  > (
  > select uc.table_name tn,
  > ucc.constraint_name cn, ucc.column_name cln,
  > ucc.position pos
  > FROM user_cons_columns ucc,
  > user_constraints uc
  > WHERE ucc.constraint_name =
  > uc.constraint_name
  > AND uc.constraint_type = 'P'
  > ) ucct
  > WHERE utc.table_name = ucct.tn (+)
  > AND utc.column_name = ucct.cln (+)
  > AND utc.table_name = '&tab_name'
  > ORDER BY ucct.pos
  > /
  >
  > Regards,
  > Charu
  >
  > -----Original Message-----
  > Sent: Monday, December 30, 2002 2:39 PM
  > To: Multiple recipients of list ORACLE-L
  >
  > 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).
  >
  >

  ---

  
  > Disclaimer
  >
  > This message (including any attachments)
  > contains
  > confidential information intended for a
  > specific
  > individual and purpose, and is protected by
  > law.
  > If you are not the intended recipient, you
  > should
  > delete this message and are hereby notified
  > that
  > any disclosure, copying, or distribution of
  > this
  > message, or the taking of any action based on
  > it,
  > is strictly prohibited.
  >
  >

  ---

  
  > Visit us at http://www.mahindrabt.com
  >
  >
  >
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.net
  > --
  > Author: Charu Joshi
  > INET: joshic_at_mahindrabt.com
  >
  > 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).
  >
  

-- 
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).