| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: columns with primary key constraint
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
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
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.
-- 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-LReceived on Mon Dec 30 2002 - 12:33:48 CST
(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).
 |
 |