Ref integrity

Hi enthu group, i want some help thru u, here we go My database is not having all the intergrity constraints set properly (needless to say I started looking at it now) and developed a query as under database ola; select unique tabname,constrtype co,idxname,coltype,collength,nrows from syscolumns a,systables b,outer( sysconstraints c) where a.tabid > 99 and a.tabid = b.tabid and b.tabid = c.tabid and colname = 'new_app_prg_code' and tabtype != 'V' and constrtype != 'N' order by 1This querry don't return correct results. It returns simply constraints on other columns also. Unfortuantely am unable to link proper SMI tables thru which i can find on which column (colname='new_app_prg_code in this case) the constraints are not set. Can somebody help on this one? TIA Mukund

I can't anser your question completely but the following works for column level Not-Null and Check constraints. Some variation of the joins may get your referential constraints. Please post it when you have it. select tabname, colname, constrname, constrtype from sysconstraints c, systables t, syscolumns cl, syscoldepend cd where c.tabid = t.tabid and cl.tabid = t.tabid and c.constrid = cd.constrid and cl.colno = cd.colno order by tabname, colname, constrname -- Jacob Salomon In article <7k8png$9j7$1@news.xmission.com>, "Shevkar, Mukund" <Mukund.Shevkar@dgs.ca.gov> wrote: > > Hi enthu group, i want some help thru u, here we go > My database is not having all the intergrity constraints set properly > (needless to say I started looking at it now) > and developed a query as under > > database ola; > select unique tabname,constrtype co,idxname,coltype,collength,nrows > from syscolumns a,systables b,outer( sysconstraints c) > where a.tabid > 99 > and a.tabid = b.tabid > and b.tabid = c.tabid > and colname = 'new_app_prg_code' > and tabtype != 'V' > and constrtype != 'N' > order by 1> This querry don't return correct results. It returns simply constraints on > other columns also. > Unfortuantely am unable to link proper SMI tables thru which i can find on > which column (colname='new_app_prg_code > in this case) the constraints are not set. > > Can somebody help on this one? > > TIA > > Mukund Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.