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

I use the following select to show me all of the relationships in a database. I wrap it with a script to pretty up the output. I can add the shell script if you need it. select pt.tabname, dt.tabname, pc1.colname, pc2.colname, pc3.colname, pc4.colname, pc5.colname, pc6.colname, pc7.colname, pc8.colname, pc9.colname, pc10.colname, pc11.colname, pc12.colname, pc13.colname, pc14.colname, pc15.colname, pc16.colname, dc1.colname, dc2.colname, dc3.colname, dc4.colname, dc5.colname, dc6.colname, dc7.colname, dc8.colname, dc9.colname, dc10.colname, dc11.colname, dc12.colname, dc13.colname, dc14.colname, dc15.colname, dc16.colname, case drefs.delrule when 'C' then 'D' else drefs.delrule end from sysconstraints dcons, sysconstraints pcons, systables pt, systables dt, outer syscolumns pc1, outer syscolumns pc2, outer syscolumns pc3, outer syscolumns pc4, outer syscolumns pc5, outer syscolumns pc6, outer syscolumns pc7, outer syscolumns pc8, outer syscolumns pc9, outer syscolumns pc10, outer syscolumns pc11, outer syscolumns pc12, outer syscolumns pc13, outer syscolumns pc14, outer syscolumns pc15, outer syscolumns pc16, outer syscolumns dc1, outer syscolumns dc2, outer syscolumns dc3, outer syscolumns dc4, outer syscolumns dc5, outer syscolumns dc6, outer syscolumns dc7, outer syscolumns dc8, outer syscolumns dc9, outer syscolumns dc10, outer syscolumns dc11, outer syscolumns dc12, outer syscolumns dc13, outer syscolumns dc14, outer syscolumns dc15, outer syscolumns dc16, sysindexes pidx, sysindexes didx, sysreferences drefs where dcons.constrtype = "R" and dcons.idxname = didx.idxname and dcons.constrid = drefs.constrid and pcons.constrid = drefs.primary and pcons.idxname = pidx.idxname and drefs.ptabid = pt.tabid and -- primary table and fields pt.tabid = pc1.tabid and pt.tabid = pc2.tabid and pt.tabid = pc3.tabid and pt.tabid = pc4.tabid and pt.tabid = pc5.tabid and pt.tabid = pc6.tabid and pt.tabid = pc7.tabid and pt.tabid = pc8.tabid and pt.tabid = pc9.tabid and pt.tabid = pc10.tabid and pt.tabid = pc11.tabid and pt.tabid = pc12.tabid and pt.tabid = pc13.tabid and pt.tabid = pc14.tabid and pt.tabid = pc15.tabid and pt.tabid = pc16.tabid and pidx.part1 = pc1.colno and pidx.part2 = pc2.colno and pidx.part3 = pc3.colno and pidx.part4 = pc4.colno and pidx.part5 = pc5.colno and pidx.part6 = pc6.colno and pidx.part7 = pc7.colno and pidx.part8 = pc8.colno and pidx.part9 = pc9.colno and pidx.part10 = pc10.colno and pidx.part11 = pc11.colno and pidx.part12 = pc12.colno and pidx.part13 = pc13.colno and pidx.part14 = pc14.colno and pidx.part15 = pc15.colno and pidx.part16 = pc16.colno and dcons.tabid = dt.tabid and -- dependent table and fields dt.tabid = dc1.tabid and dt.tabid = dc2.tabid and dt.tabid = dc3.tabid and dt.tabid = dc4.tabid and dt.tabid = dc5.tabid and dt.tabid = dc6.tabid and dt.tabid = dc7.tabid and dt.tabid = dc8.tabid and dt.tabid = dc9.tabid and dt.tabid = dc10.tabid and dt.tabid = dc11.tabid and dt.tabid = dc12.tabid and dt.tabid = dc13.tabid and dt.tabid = dc14.tabid and dt.tabid = dc15.tabid and dt.tabid = dc16.tabid and didx.part1 = dc1.colno and didx.part2 = dc2.colno and didx.part3 = dc3.colno and didx.part4 = dc4.colno and didx.part5 = dc5.colno and didx.part6 = dc6.colno and didx.part7 = dc7.colno and didx.part8 = dc8.colno and didx.part9 = dc9.colno and didx.part10 = dc10.colno and didx.part11 = dc11.colno and didx.part12 = dc12.colno and didx.part13 = dc13.colno and didx.part14 = dc14.colno and didx.part15 = dc15.colno and didx.part16 = dc16.colno $whereComp order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18, 19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34 ;