Re: to browse constraints in informix

On Mon, 4 Dec 2000 09:47:40 +0100, "Lukasz Feldman" <lukasz@atm.com.pl> wrote: >Hello! > >Does anybody know a freeware or shareware software which can give me a list >of constraints in all of the tables in informix database. I wonder about >something like >Oracle Enterprise Manager bu for Informix. >My task is to find a broken referenced key. It is so much complicated to >browse >through the tables, and tables and tables in dbaccess. I can send You my "software" written to find a referenced keys. The software is written in SQL: select c1.constrname, r.delrule, t1.tabname, trim( trailing ',' fromnvl(trim(cl1.colname),'')||','||nvl(trim(cl2.colname),'')||','||nvl(trim(cl3 .colname),'')||','||nvl(trim(cl4.colname),'')), t2.tabname, trim( trailing ',' from nvl(trim(co1.colname),'')||','||nvl(trim(co2.colname),'')||','||nvl(trim(co3 .colname),'')||','||nvl(trim(co4.colname),'')) from sysconstraints c1, systables t1, sysindexes i1, sysconstraints c2, sysindexes i2, outer(syscolumns cl1), outer(syscolumns cl2), outer(syscolumns cl3), outer(syscolumns cl4), sysreferences r, systables t2, outer(syscolumns co1), outer(syscolumns co2), outer(syscolumns co3), outer(syscolumns co4) where i1.idxname = c1.idxname and t1.tabid = i1.tabid and cl1.tabid = i1.tabid and cl1.colno = i1.part1 and cl2.tabid = i1.tabid and cl2.colno = i1.part2 and cl3.tabid = i1.tabid and cl3.colno = i1.part3 and cl4.tabid = i1.tabid and cl4.colno = i1.part4 and r.constrid = c1.constrid and t2.tabid = r.ptabid and c2.constrid = r.primary and i2.idxname = c2.idxname and co1.tabid = i2.tabid and co1.colno = i2.part1 and co2.tabid = i2.tabid and co2.colno = i2.part2 and co3.tabid = i2.tabid and co3.colno = i2.part3 and co4.tabid = i2.tabid and co4.colno = i2.part4 order by t1.tabname, c1.constrname; This query will return to You all foreign keys. Piotrek Wawrzyniak Piotr.Wawrzyniak@put.poznan.pl