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 = '<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 <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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jamadagni, Rajendra
INET: Rajendra.Jamadagni_at_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_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:58:54 CST
