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)
[EMAIL PROTECTED]
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Steven Haas
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
