Primary key column names

Solaris 8 Informix 10.00.UC8 I thought this would be easy - and it may be - but locating the column names that make up a table's primary key via a syscatalog query has baffled this caveman. Seems sql is frightening and confusing today (long live unfrozen caveman lawyer). Most of our tables have composite pk's - someone has requested a spread sheet (yack) with every database:table:column and columns that are pk's in our bajillion databases - I got the db:table:column part - just need a nice clean way to get the pk column names - other than the kludgy awk/sed crap I am half way through with that uses dbschema... MM

OK, mapping primary keys to columns: 1. sysconstraints will identify which index supports the primary key: select idxname from sysconstraints where tabid = <mytables tabid> andconstrtype = 'P'; 1. map the colno's from sysindexes to syscolumns (remember that the colno for a descending column is negated in sysindexes so use ABS(sysindexes.part1), ABS(sysindexes.part2).... Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 3, 2009 at 11:33 AM, MIKE MAGIE <jmmagie@yahoo.com> wrote: > Solaris 8 > Informix 10.00.UC8 > > I thought this would be easy - and it may be - but locating the column > names > that make up a table's primary key via a syscatalog query has baffled this > caveman. Seems sql is frightening and confusing today (long live unfrozen > caveman lawyer). > > Most of our tables have composite pk's - someone has requested a spread > sheet > (yack) with every database:table:column and columns that are pk's in our > bajillion databases - I got the db:table:column part - just need a nice > clean > way to get the pk column names - other than the kludgy awk/sed crap I am > half > way through with that uses dbschema... > > MM > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016368e1d229bcdaa0466a8d41d

Thanks Art - I will start wrestling with that - appreciate the fast response as always. Mike

Hi, So taking Art's recommendation into account you should get something like: select t1.tabname, t4.colname, case when abs(t3.part1) = t4.colno THEN "1st pk field" when abs(t3.part2) = t4.colno THEN "2nd pk field" when abs(t3.part3) = t4.colno THEN "3rd pk field" when abs(t3.part4) = t4.colno THEN "4th pk field" when abs(t3.part5) = t4.colno THEN "5th pk field" when abs(t3.part6) = t4.colno THEN "6th pk field" when abs(t3.part7) = t4.colno THEN "7th pk field" when abs(t3.part8) = t4.colno THEN "8th pk field" when abs(t3.part9) = t4.colno THEN "9th pk field" when abs(t3.part10) = t4.colno THEN "10th pk field" when abs(t3.part11) = t4.colno THEN "11th pk field" when abs(t3.part12) = t4.colno THEN "12th pk field" when abs(t3.part13) = t4.colno THEN "13th pk field" when abs(t3.part14) = t4.colno THEN "14th pk field" when abs(t3.part15) = t4.colno THEN "15th pk field" when abs(t3.part16) = t4.colno THEN "16th pk field" else ' ' end pk_indicator from systables t1, syscolumns t4, outer (sysconstraints t2, sysindexes t3) where t1.tabid = t2.tabid and t1.tabid = t3.tabid and t2.idxname = t3.idxname and t4.tabid = t1.tabid and t2.constrtype = 'P' and t1.tabid > 100 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: 03 April 2009 06:14 PM To: ids@iiug.org Subject: Re: Primary key column names [15416] OK, mapping primary keys to columns: 1. sysconstraints will identify which index supports the primary key: select idxname from sysconstraints where tabid = <mytables tabid> andconstrtype = 'P'; 1. map the colno's from sysindexes to syscolumns (remember that the colno for a descending column is negated in sysindexes so use ABS(sysindexes.part1), ABS(sysindexes.part2).... Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 3, 2009 at 11:33 AM, MIKE MAGIE <jmmagie@yahoo.com> wrote: > Solaris 8 > Informix 10.00.UC8 > > I thought this would be easy - and it may be - but locating the column > names that make up a table's primary key via a syscatalog query has > baffled this caveman. Seems sql is frightening and confusing today > (long live unfrozen caveman lawyer). > > Most of our tables have composite pk's - someone has requested a > spread sheet > (yack) with every database:table:column and columns that are pk's in > our bajillion databases - I got the db:table:column part - just need a > nice clean way to get the pk column names - other than the kludgy > awk/sed crap I am half way through with that uses dbschema... > > MM > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016368e1d229bcdaa0466a8d41d **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ================== Please read our Email Disclaimer : http://www.thefuelgroup.com/disclaimer.html

Yeah pretty much - although I balk at using the word indicator... kidding - thanks a million. MM