| Author |
Message |
Help-Me-Out
Active User
Joined: 09 Dec 2006
Posts: 53
Location: Pune
|
|
|
|
Hi,
how to find the relation between the two tables i.e. how to find the which is primary key and foreign kay of the tables? |
|
| Back to top |
|
 |
References
|
 Posted: Thu May 08, 2008 5:15 pm Post subject: Re: How to find the relation between tables? |
|
|
|
|
ashimer
Warnings : 1
Active User
Joined: 13 Feb 2004
Posts: 113
|
|
|
|
| Code: |
select colname from sysibm.sysforeignkeys where tbname = 'table name';
|
will give you the col which is the foreign key
now for primary key
select colname from sysibm.syskeys where ixname in
(select name from sysibm.sysindexes where tbname ='table name'); |
|
| Back to top |
|
|
the_gautam
Active User
Joined: 05 Jun 2005
Posts: 100
Location: Bangalore
|
|
|
|
you may get the details of the primary key with the following query as well :
SELECT NAME FROM SYSIBM.SYSCOLUMNS WHERE TBCREATOR = <TABLE CREATOR NAME> AND TBNAME = <TABLE NAME> AND KEYSEQ > 0;
(OR)
SELECT A.COLNAME, A.COLSEQ, A.ORDERING, FROM SYSIBM.SYSKEYS A, SYSIBM.SYSINDEXES B WHERE A.IXNAME = B.NAME AND A.IXCREATOR = B.CREATOR AND A.IXCREATOR = B.TBCREATOR AND B.TBNAME = <TABLE NAME> AND B.TBCREATOR = <TABLE CREATOR> AND B.UNIQUERULE = ‘P’; |
|
| Back to top |
|
|
Help-Me-Out
Active User
Joined: 09 Dec 2006
Posts: 53
Location: Pune
|
|
|
|
| Thanks all for ur reply.. this is working |
|
| Back to top |
|
|
|
|
