Re: Get all child table and key names of a parent table
"Kuldeep" <kuldeepchitrakar@gmail.com> wrote in message
news:1144752804.917120.279890@i39g2000cwa.googlegroups.com...
> 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..
How about this:
CREATE PROCEDURE index_columns(index_name VARCHAR(128))
RETURNING VARCHAR(255);
-- Fetch the list of columns names in an index
-- Doug Lawry, comp.databases.informix, 11 April 2006
DEFINE column_name VARCHAR(128);
DEFINE column_list VARCHAR(255);
LET column_list = '';
FOREACH
SELECT colname
INTO column_name
FROM sysindexes, syscolumns
WHERE idxname = index_name
AND sysindexes.tabid = syscolumns.tabid
AND colno IN (
part1, part2, part3, part4,
part5, part6, part7, part8,
part9, part10, part11, part12,
part13, part14, part15, part16
)
LET column_list = column_list || ', ' || column_name;
END FOREACH
RETURN column_list[3,255];
END PROCEDURE;
SELECT TAB1.tabname parent, index_columns(CON1.idxname) primary_key,
TAB2.tabname child, index_columns(CON2.idxname) foreign_key
FROM sysreferences REFS,
sysconstraints CON1,
sysconstraints CON2,
systables TAB1,
systables TAB2
WHERE CON1.constrid = REFS.primary
AND CON2.constrid = REFS.constrid
AND TAB1.tabid = CON1.tabid
AND TAB2.tabid = CON2.tabid;
--
Regards,
Doug Lawry
www.douglawry.webhop.org