Re: ?? Getting info on foreign keys from sys*

John Mullee wrote: > > How can I retieve, using something like the following query, > a list of tables which reference a table 'X' via foreign keys? [SNIP attempt to SELECT this info] Check the source code to the print_constraints function in my myschema.ec program for how to do this. Myschema is part of the utils2_ak package I submitted to the IIUG Software Archives. The quick and dirty is that for the columns in the referencing table (the one containing the foreign key constraint) you go to the sysindexes record for the index named in the sysconstraints record corresponding to the foreign key. For the columns in the referenced table (the one containing the corresponding primary key) you go to the sysindexes record named in the sysconstraints record corresponding to the primary key of the referenced table. It's a bit more involved than that but feel free to lift the code from myschema.ec. NOTICE! I spotted a bug in that code: The SELECT...INTO immediately preceeding the 'EXEC SQL OPEN cols USING :ptabid;' statement needs an additional where clause. Add: "AND sc.constrtype = 'P'" to that SELECT to avoid getting the wrong column list if the referenced table itself references another (grandparent) table. I'll fix the code and submit an update to the Repository today but it will not show up for a week or so. Anyone using myschema.ec note the fix goes at around line 2407. Art S. Kagel