Re: convolution redefined

I think you've got it about right, and you've simplified the problem by only retrieving the columns which are the leading part of a compound foreign key. For example, if your CREATE TABLE statement included FOREIGN KEY (Col01, Col02) REFERENCES SomeOtherTable your code only selects Col01, not Col02 as well. To fix that, you'd probably do a union of your current query with a variant that cited i.part2 instead of i.part1. And theoretically, you would need to do it for parts 3..16 too, though that is very unlikely to be necessary. I wouldn't change your code - it returns the useful information that you need and I'm only being a purist at the expense of performance. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> }From: adil@msil.sps.mot.com }Date: Wed, 22 Jan 1997 11:02:21 -0600 }X-Informix-List-Id: <news.32863> } }I'm trying to get what I think is simple information from the System }catalogs, but it's turning out to be a real killer trying to get it out. }To start off, I'll identify the system: I'm working in Informix Online vs. }5.03. } }I want to get, given a table id, the names of all the columns in the }table which are foreign keys and the tables they reference. I thought }that would be simple enough, but after sifting through the Online }reference, the best I could do was the monster which follows (assuming a }table id of 181). PLEASE someone tell me there's an easier way... } }select } k.colname, } t.tabname }from } syscolumns k, } systables t, } sysindexes i, } sysconstraints c, } sysreferences r }where } k.tabid=i.tabid and } i.tabid=181 and } i.part1=k.colno and } i.idxname=c.idxname and } c.constrtype="R" and } c.constrid=r.constrid and } r.ptabid=t.tabid