Re: Get all child table and key names of a parent table

I have such a query, but it works not very fast... Select pt.tabname Parent, replace(replace(substr( multiset(select item c.colname from syscolumns c where pt.tabid=c.tabid and (c.colno in (select part1 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part2 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part3 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part4 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part5 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part6 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part7 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part8 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part9 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part10 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part11 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part12 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part13 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part14 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part15 from sysindexes pi where pc.idxname=pi.idxname) or c.colno in (select part16 from sysindexes pi where pc.idxname=pi.idxname)) )::LVARCHAR,11), ''''), '}') Primary_key, t.tabname Child, replace(replace(substr( multiset(select item c.colname from syscolumns c where t.tabid=c.tabid and (c.colno in (select part1 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part2 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part3 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part4 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part5 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part6 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part7 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part8 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part9 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part10 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part11 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part12 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part13 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part14 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part15 from sysindexes fi where a.idxname=fi.idxname) or c.colno in (select part16 from sysindexes fi where a.idxname=fi.idxname)) )::LVARCHAR,11), ''''), '}') Child_key >From systables pt, sysreferences r, sysconstraints pc, sysconstraints a, systables t Where pt.tabid=r.ptabid and r.primary=pc.constrid and r.constrid=a.constrid and a.tabid=t.tabid Kuldeep wrote: > select stab.tabname Parent, > scol.colname Primary_key, > sstab.tabname Child, > sscol.colname Child_key > from syscolumns scol, > syscolumns sscol, > sysindexes sind, > sysindexes ssind, > sysconstraints scon, > sysconstraints sscon, > systables stab, > systables sstab, > sysreferences sref > where scol.tabid=sind.tabid > and scol.colno = sind.part1 > and sind.idxname=scon.idxname > and stab.tabid=scon.tabid > and sstab.tabid=sscon.tabid > and sscol.tabid = ssind.tabid > and (sscol.colno = ssind.part1 or sscol.colno = ssind.part2) > and sscon.idxname=ssind.idxname > and sref.constrid=sscon.constrid > and stab.tabid=sref.ptabid > and stab.tabname='ParentTableName' > > above query works gr8 when single column primary key in Parent table, > but when there is two or morecolumn primary key it does not gives right > ans. plz try to solve..