RE: ?? Getting info on foreign keys from sys*

John, Here is a select statement to get the columns for indexes. select tabname, idxname, c1.colname, c2.colname, c3.colname, c4.colname, c5.colname, c6.colname, c7.colname, c8.colname, c9.colname, c10.colname, c11.colname, c12.colname, c13.colname, c14.colname, c15.colname, c16.colname from systables t, syscolumns c1, outer(syscolumns c2), outer(syscolumns c3), outer(syscolumns c4), outer(syscolumns c5), outer(syscolumns c6), outer(syscolumns c7), outer(syscolumns c8), outer(syscolumns c9), outer(syscolumns c10), outer(syscolumns c11), outer(syscolumns c12), outer(syscolumns c13), outer(syscolumns c14), outer(syscolumns c15), outer(syscolumns c16), sysindexes i where t.tabid > 99 and t.tabtype = 'T' and t.tabid = i.tabid and i.tabid = c1.tabid and i.tabid = c2.tabid and i.tabid = c3.tabid and i.tabid = c4.tabid and i.tabid = c5.tabid and i.tabid = c6.tabid and i.tabid = c7.tabid and i.tabid = c8.tabid and i.tabid = c9.tabid and i.tabid = c10.tabid and i.tabid = c11.tabid and i.tabid = c12.tabid and i.tabid = c13.tabid and i.tabid = c14.tabid and i.tabid = c15.tabid and i.tabid = c16.tabid and part1 = c1.colno and part2 = c2.colno and part3 = c3.colno and part4 = c4.colno and part5 = c5.colno and part6 = c6.colno and part7 = c7.colno and part8 = c8.colno and part9 = c9.colno and part10 = c10.colno and part11 = c11.colno and part12 = c12.colno and part13 = c13.colno and part14 = c14.colno and part15 = c15.colno and part16 = c16.colno order by tabname, idxname; HTH David Ashby ______________________________ Reply Separator _________________________________ Subject: Re: ?? Getting info on foreign keys from sys* Author: Jonathan Leffler [SMTP:jleffler@earthlink.net] at WC-KEN Date: 09/7/98 4:29 PM John Mullee wrote: > How can I retrieve, using something like the following query, > a list of tables which reference a table 'X' via foreign keys? > > select T.tabname, C.constrtype, C.constrname, >R.constrid, I.idxname > from sysconstraints C, sysreferences R, > sysindices I, systables T SysIndexes? > where T.tabid = I.tabid > and T.tabid = C.tabid > and R.constrid = C.constrid > and I.idxname = C.idxname > and T.tabname='X' > order by 1, 2, 3, 4, 5; > > this returns: > > popul R r331_1750 1750 331_1750 > popul R r331_1751 1751 331_1751 > popul R r331_1752 1752 331_1752 > popul R r331_1753 1753 331_1753 > popul R r331_1754 1754 331_1754 > > .. but tells me nothing about the fields which are referred from, > or the tables to which they refer.... Well, the index names can be used to determine the columns (but it's hard, because it is a 15-way outer join if you do it properly -- yes, there can be 16 columns in an index, but the first column is always defined and therefore can be determined with a regular inner join). With an FK constraint, there's a non-unique index on the referencing table and a unique index on the referenced table, so that gives you all the column info you might need. Doesn't it? I don't have the manuals immediately at hand to give a more complete answer, which is also partly laziness because typing in two sets of SysIndexes -> SysColumns joins is not a pleasant idea, and because there should be two references to SysTables, and two references to SysIndexes (ignoring the multi-way joins to SysColumns). -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix -- see http://www.perl.com/CPAN #include <disclaimer.h>