Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id gBUNXaN05747 for ; Mon, 30 Dec 2002 17:33:36 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id gBUNXZc05739 for ; Mon, 30 Dec 2002 17:33:35 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id MAA33525; Mon, 30 Dec 2002 12:15:33 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 00524558; Mon, 30 Dec 2002 11:58:54 -0800 Message-ID: Date: Mon, 30 Dec 2002 11:58:54 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Jamadagni, Rajendra" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Jamadagni, Rajendra" Subject: RE: columns with primary key constraint Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPartTM-000-5a15269b-8c40-4002-9af7-3698a35ad265" ------=_NextPartTM-000-5a15269b-8c40-4002-9af7-3698a35ad265 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C2B03D.E3524AF0" ------_=_NextPart_001_01C2B03D.E3524AF0 Content-Type: text/plain; charset="iso-8859-1" SELECT /*+ no_merge(utc) no_merge(ucct.uc) no_merge(ucct.ucc) */ utc.owner ,utc.table_name ,utc.column_name ,DECODE(ucct.constraint_type, 'P',DECODE(NVL(ucct.cln, ' '), ' ',' ','(PK-['||ucct.cn || '] ' || ucct.pos||')'),'') is_pk ,DECODE(ucct.constraint_type, 'R',DECODE(NVL(ucct.cln, ' '), ' ',' ','(FK-['||ucct.cn || '] ' || ucct.pos||')'),'') is_fk FROM DBA_TAB_COLUMNS utc, (SELECT uc.owner, uc.table_name tn, ucc.constraint_name cn, ucc.column_name cln, ucc.position pos, uc.constraint_type FROM DBA_CONS_COLUMNS ucc, DBA_CONSTRAINTS uc WHERE ucc.owner = uc.owner AND ucc.constraint_name = uc.constraint_name AND uc.constraint_type IN ('P','R')) ucct WHERE utc.table_name = ucct.tn (+) AND utc.column_name = ucct.cln (+) AND utc.owner = ucct.owner AND utc.OWNer = '' ORDER BY utc.owner, utc.table_name, ucct.pos / Just an improvement on Charu's script ... Raj ______________________________________________________ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -----Original Message----- Sent: Monday, December 30, 2002 2:34 PM To: Multiple recipients of list ORACLE-L 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 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@opus-consultants.com > steven.haas@snet.net > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > -- > Author: Steven Haas > INET: steven.haas@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@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@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@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@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@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). ------_=_NextPart_001_01C2B03D.E3524AF0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: columns with primary key constraint

SELECT /*+ no_merge(utc) no_merge(ucct.uc) = no_merge(ucct.ucc) */
       = utc.owner
      = ,utc.table_name
      = ,utc.column_name
      = ,DECODE(ucct.constraint_type, 'P',DECODE(NVL(ucct.cln, ' '), ' ',' = ','(PK-['||ucct.cn || '] ' || ucct.pos||')'),'') is_pk

      = ,DECODE(ucct.constraint_type, 'R',DECODE(NVL(ucct.cln, ' '), ' ',' = ','(FK-['||ucct.cn || '] ' || ucct.pos||')'),'') is_fk

FROM   DBA_TAB_COLUMNS utc,
(SELECT uc.owner, uc.table_name tn, = ucc.constraint_name cn, ucc.column_name cln,
           = ucc.position pos, uc.constraint_type
    FROM   DBA_CONS_COLUMNS = ucc,
           = DBA_CONSTRAINTS uc
    WHERE  ucc.owner =3D = uc.owner
    AND    = ucc.constraint_name =3D uc.constraint_name
    AND    = uc.constraint_type IN ('P','R')) ucct
WHERE utc.table_name =3D ucct.tn (+)
AND utc.column_name =3D ucct.cln (+)
AND utc.owner =3D ucct.owner
AND utc.OWNer =3D '<OWNER>'
ORDER BY utc.owner, utc.table_name, ucct.pos
/

Just an improvement on Charu's script ...
Raj
______________________________________________________
Rajendra Jamadagni      =         MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't = reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion = is an art!


-----Original Message-----
From: Steven Haas [mailto:steven.haas@snet.net]
Sent: Monday, December 30, 2002 2:34 PM
To: Multiple recipients of list ORACLE-L
Subject: 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@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 =3D
> uc.constraint_name
>     AND    = uc.constraint_type =3D 'P'
> ) ucct
> WHERE utc.table_name =3D ucct.tn (+)
> AND utc.column_name =3D ucct.cln (+)
> AND utc.table_name =3D '&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 =3D 'TAB_A'
> and    uc.constraint_type =3D = 'P'
> and    utc.table_name =3D = uc.table_name
> and    uc.constraint_name (+) = =3D
> ucc.constraint_name
> and    utc.column_name (+) =3D = 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.
>
> =3D=3D=3D=3D=3D
> Steve Haas
> Opus Consultants, LLC
> 860.408.1512 (office/fax)
> 860.651.9475 (home)
> steven_haas@opus-consultants.com
> steven.haas@snet.net
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.net
> --
> Author: Steven Haas
>   INET: steven.haas@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@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@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@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@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@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).

------_=_NextPart_001_01C2B03D.E3524AF0-- ------=_NextPartTM-000-5a15269b-8c40-4002-9af7-3698a35ad265 Content-Type: text/plain; name="ESPN_Disclaimer.txt" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="ESPN_Disclaimer.txt" *********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1 ------=_NextPartTM-000-5a15269b-8c40-4002-9af7-3698a35ad265-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni@espn.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@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).