How to get foreign key reference table in systables

[F Miguel Oliveira]

Hello,

I'm trying to get the table where a foreign key is referencing. How can i do
it in systables?

thanks in advance,
Miguel

[Gustavo Tobares]

there is a fragment of a 4gl program to do that:

prompt "Ingrese BD: " for ldb
prompt "Ingrese nombre de tabla " for ltabla

database ldb

select * -- busco los datos de la tabla
into lr_tab_uno.*
from systables
where tabname = ltabla

declare c_ref_a cursor for -- busco en la tabla de referencias
select *
from sysreferences
where ptabid = lr_tab_uno.tabid

foreach c_ref_a into lr_ref.*

select *
into lr_con.*
from sysconstraints
where constrid = lr_ref.constrid -- busco los constraints
select *
into lr_tab_dos.*
from systables
where tabid = lr_con.tabid -- busco tabla que la referencia

display " - ",lr_tab_dos.tabname

end foreach

Gustavo Tobares
Administrador de Sistemas y DBA
Centro de Computos - Red Megatone
TE: 0342-4500972 - Fax: 0342-4500940

-----Mensaje original-----
De: F Miguel Oliveira [mailto:f...@eurogtd.pt]
Enviado el: Jueves, 06 de Febrero de 2003 03:57 p.m.
Para: inform...@iiug.org
Asunto: How to get foreign key reference table in systables

[Jonathan Leffler]

F Miguel Oliveira wrote:
> I'm trying to get the table where a foreign key is referencing. How can i do
> it in systables?

There is code in SQLCMD (sqlinfo.ec) to do this -- you can't do it
with just systables, you need sysconstraints and sysreferences too.
The code in SQLCMD does it with a single select statement once you
have the tabid for either the source table or the target table, and it
uses two references to sysconstraints, and two references to systables.

The output data is inscrutable but relevant:

For the referencing table - constraint name, constraint owner, index
name (but not the index owner for a reason that escapes me right now),
tabid, table owner and table name;

For the referenced table - the same set of data.

You can decide which of those are relevant to you.

--
Jonathan Leffler #include <disclaimer.h>
Email: jlef...@earthlink.net, jlef...@us.ibm.com
Guardian of DBD::Informix 1.04.PC1 -- http://dbi.perl.org/

[Michal Hajek]

F Miguel Oliveira wrote:
> Hello,
>
> I'm trying to get the table where a foreign key is referencing. How can i do
> it in systables?

I use this (my) SQL command (sorry for the czech comments :-):
-----
select a.tabname[1,12],b.colname[1,12],
h.tabname[1,12],i.colname[1,12],d.constrname
from systables a, syscolumns b, sysindexes c,sysconstraints d,
sysreferences e, sysconstraints f,sysindexes g,systables h,
syscolumns i
where
d.constrname="r2939_3002" -- jméno constraintu
and b.tabid=a.tabid -- spojím tabulku a sloupec
and c.tabid=a.tabid -- vyberu podle tabulky v sysindexes
and (c.part1=b.colno or c.part2=b.colno) -- a tam podle sloupce
and d.idxname=c.idxname -- vyberu podle indexu v sysconstraints
and e.constrid=d.constrid --
and e.primary=f.constrid -- najdu vztažný constraint
and f.idxname=g.idxname -- a příslušný prim. index
and h.tabid=g.tabid -- číslo vztažné tabulky
and i.tabid=h.tabid -- jméno vztažné tabulky
and i.colno=g.part1 -- jméno sloupce (pozor na další !)
----

And checking references for table this shell script
###################################
## constraints for table(s) ###
## (c) Michal Hajek, 1997 ###
###################################
## *** warning: names of tables and constraints may be clipped

# define you database name here
DATABASE=db

## constraints from the table(s)
if [ "$1" != "" ] ; then
echo "Referencing to $1:"
echo "select \"Z:\" zz,a.tabname,e.tabname,f.colname,c.constrname[1,15] cc
from systables a,sysreferences b,sysconstraints c,sysindexes d,
systables e,syscolumns f
where a.tabname matches \"$1\"
and b.ptabid=a.tabid
and c.constrid=b.constrid
and d.idxname=c.idxname
and e.tabid=d.tabid
and f.tabid=d.tabid
and f.colno=d.part1
order by a.tabname,e.tabname,f.colname" \
|isql $DATABASE 2>/dev/null | grep "Z:"

## referenced
## only 1. column of index (constraint)

echo
echo "Referenced from $1:"
echo "select \"DO:\" z,a.tabname[1,12],b.colname[1,12], # ***
h.tabname[1,12],i.colname[1,12],d.constrname # ***
from systables a, syscolumns b, sysindexes c,sysconstraints d,
sysreferences e, sysconstraints f,sysindexes g,systables h,
syscolumns i
where a.tabname matches \"$1\"
and b.tabid=a.tabid
and c.tabid=a.tabid
and (c.part1=b.colno or c.part2=b.colno)
and d.idxname=c.idxname
-- and d.constrtype="R"
and e.constrid=d.constrid
and e.primary=f.constrid
and f.idxname=g.idxname
and h.tabid=g.tabid
and i.tabid=h.tabid
and i.colno=g.part1" \
|isql $DATABASE 2>/dev/null | grep "DO:"

else
echo "missing parameter <tabname>"
fi

MH

--
--------------------------------------------------------------
Michal Hajek mailto:ha...@nspuh.cz
--------------------------------------------------------------