View previous topic :: View next topic
|
Author |
Message |
priya
Moderator
Joined: 24 Jul 2003 Posts: 568 Location: Bangalore
|
|
|
|
Dear friends,
I need to find out the primary key of an existing table. (That's not my own) |
|
Back to top |
|
|
mdtendulkar
Active User
Joined: 29 Jul 2003 Posts: 237 Location: USA
|
|
|
|
Hello Priya,
Execute the following query, you will get the primary key for the table
SYSIBM.SYSCOLUMNS identifies columns of a parent key in column KEYSEQ; a nonzero value indicates the place of a column in the parent key.
Code: |
SELECT TBCREATOR, TBNAME, NAME, KEYSEQ
FROM SYSIBM.SYSCOLUMNS
WHERE TBCREATOR = 'creater id'
AND TBNAME = 'table-name'
AND KEYSEQ > 0
ORDER BY KEYSEQ; |
SYSIBM.SYSINDEXES identifies the primary index of a table by the value P in column UNIQUERULE.
Code: |
SELECT TBCREATOR, TBNAME, NAME, CREATOR, DBNAME, INDEXSPACE
FROM SYSIBM.SYSINDEXES
WHERE TBCREATOR = 'creator id'
AND TBNAME = 'table-name'
AND UNIQUERULE = 'P'; |
Hope this will help you.
Regards
Mayuresh Tendulkar
|
|
Back to top |
|
|
priya
Moderator
Joined: 24 Jul 2003 Posts: 568 Location: Bangalore
|
|
|
|
But sir,
I have no Authorities to select from SYSIBM tables in our site. How can I find out the primary key sir... |
|
Back to top |
|
|
mcmillan
Site Admin
Joined: 18 May 2003 Posts: 1210 Location: India
|
|
|
|
While selecting all columns of a table,
It displayed in ASCNDING order of the primary key by default. (If you have any other parallel row in asc order, just try by inserting two more rows in des order for all columns) |
|
Back to top |
|
|
priya
Moderator
Joined: 24 Jul 2003 Posts: 568 Location: Bangalore
|
|
|
|
Thanks a lot,
Can anybody (specially Mcmillan or Tendulkar) Please tell me how to find out the foreign key of a table from SYSIBM. |
|
Back to top |
|
|
mdtendulkar
Active User
Joined: 29 Jul 2003 Posts: 237 Location: USA
|
|
|
|
Hello Priya,
Below is the answer of your query..
Code: |
SELECT TBNAME, COLNAME, COLNO, CREATOR
FROM SYSIBM.SYSFOREIGNKEYS
WHERE TBNAME = 'table-name'; |
SYSFOREIGNKEYS table contains one row for every column of every foreign key.
Let me know if this helps you.
Regards
Mayuresh Tendulkar |
|
Back to top |
|
|
mcmillan
Site Admin
Joined: 18 May 2003 Posts: 1210 Location: India
|
|
|
|
Dear priya,
To find the relative table of that foreign key, please use SYSIBM.SYSRELS |
|
Back to top |
|
|
|