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).
Received on Mon Dec 30 2002 - 13:33:52 CST