Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Primary Keys
- Thread starter gmoorthy
- Start date
Hi,
The SQL below lists all the tables with primary & foreign key relationships.
select ptabid TABID,
substr((select tabname from systables where tabid=ptabid),1,18) PRIMARY,
tabname[1,18] FOREIGN,
constrname[1,18] IDXNAME,
decode(delrule,'C','Yes','No') CASCADE
from
sysconstraints c, sysreferences r, systables s
where
c.constrtype='R' and c.constrid=r.constrid and c.tabid=s.tabid and
ptabid in
(select tabid from sysconstraints where constrtype in ('P','U'))
order by 1;
Regards,
Shriyan
The SQL below lists all the tables with primary & foreign key relationships.
select ptabid TABID,
substr((select tabname from systables where tabid=ptabid),1,18) PRIMARY,
tabname[1,18] FOREIGN,
constrname[1,18] IDXNAME,
decode(delrule,'C','Yes','No') CASCADE
from
sysconstraints c, sysreferences r, systables s
where
c.constrtype='R' and c.constrid=r.constrid and c.tabid=s.tabid and
ptabid in
(select tabid from sysconstraints where constrtype in ('P','U'))
order by 1;
Regards,
Shriyan
- Status