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 <[EMAIL PROTECTED]> 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) > [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). > -- 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).
