Re: Get all child table and key names of a parent table
Kuldeep wrote:
Have you tried:
myschema -d <mydatabase> -t <ParentTableName> -F
Myschema is a replacement for dbschema which adds significant value
(including the -F [follow references] option) and is included in the package
utils2_ak downloadable from the IIUG Software Repository.
Art S. Kagel
> 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..
>