RE: to browse constraints in informix
You can also go to iiug.org and pick up dbdiff2. In it is 4gl code to read
the constraints catalogues (and others) and build DDL from it.
cheers
j.
> -----Original Message-----
> From: Piotr Wawrzyniak [mailto:Piotr.Wawrzyniak@put.poznan.pl]
> Sent: Monday, December 04, 2000 5:06 AM
> To: informix-list@iiug.org
> Subject: 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 ',' from> nvl(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
>
>