Re: ?? Getting info on foreign keys from sys*
John
I have a similar query but it uses 2 temporary tables to do this. I
used it to display schema information on my web page. Here it is:
SELECT a.constrid pk_id, a.constrname primary_key,
a.idxname index_name
FROM sysconstraints a, systables b
WHERE a.tabid = b.tabid
AND a.constrtype = "P"
AND b.tabname = "X" -- this is your tablename
INTO TEMP temptab1;
SELECT c.primary pk_id, b.tabname ref_d_by_table,
a.constrname foreign_key
FROM sysconstraints a, systables b, sysreferences c
WHERE a.tabid = b.tabid
AND a.constrid = c.constrid
AND c.ptabid =
(SELECT tabid
FROM systables
WHERE tabname = "T")
INTO TEMP temptab2;
SELECT a.primary_key, a.index_name, b.ref_d_by_table, b.foreign_key
FROM temptab1 a, OUTER temptab2 b
WHERE a.pk_id = b.pk_id;
I guess there must be a better way, though :-).
HTH
Sujit Pal
______________________________ Reply Separator
_________________________________
Subject: ?? Getting info on foreign keys from
sys*
Author: jmullee@nirvanet.net (John Mullee) at
internet
Date: 7/8/98 3:31 PM
How can I retieve, using something like the
following query,
a list of tables which reference a table 'X' via
foreign keys?
select T.tabname, C.constrtype, C.constrname,R.constrid, I.idxname
from sysconstraints C, sysreferences R,
sysindices I, systables T
where T.tabid = I.tabid
and T.tabid = C.tabid
and R.constrid = C.constrid
and I.idxname = C.idxname
and T.tabname='X'
order by 1, 2, 3, 4, 5;
this returns:
popul R r331_1750 1750 331_1750
popul R r331_1751 1751 331_1751
popul R r331_1752 1752 331_1752
popul R r331_1753 1753 331_1753
popul R r331_1754 1754 331_1754
.. but tells me nothing about the fields which are referred from,
or the tables to which they refer....
Help please??
John