list all table primary key

[c***@hotmail.com]

Hi All,

Is the any sql to list all the tables primary key name?
Thanks.

[Knut Stolze]

Post by c***@hotmail.com
Hi All,
Is the any sql to list all the tables primary key name?

You can query SYSCAT.TABCONST:

SELECT *
FROM syscat.tabconst
WHERE type = 'P'

[c***@hotmail.com]

Thanks Knut.

Now, we got the primary key for each table,
So is it possible to extract the actual column to this primary key?

BTW, Is there any doc reference to SYSCAT.TABCONST...?
Or any other SYSCAT or SYSIBM references?
Thanks.

[Blair Kenneth Adamache]

Select on syscat.indexes as well, i.e.

db2 select indname,colnames from syscat.indexes where indname in
(select constname from syscat.tabconst)

Post by c***@hotmail.com
Thanks Knut.
Now, we got the primary key for each table,
So is it possible to extract the actual column to this primary key?
BTW, Is there any doc reference to SYSCAT.TABCONST...?
Or any other SYSCAT or SYSIBM references?
Thanks.

[db2user99]

SELECT COLUMN_NAME,
KEY_SEQ,
PK_NAME
FROM SYSIBM."SQLPRIMARYKEYS"
WHERE "TABLE_SCHEM" = 'Schema Name'
AND "TABLE_NAME" = 'Table Name'

[c***@hotmail.com]

Thanks Blair Kenneth Adamache, db2venky.

Really open my eye to Syscat stuff ...
After reading yours solution on this forum,
I read number of references about syscat.

Wow! ....

I think this is the only expression I can think of....
Thanks again.

[c***@hotmail.com]

Knut,

Can I do something like this...?

SELECT * FROM SYSCAT.COLUMNS where KeySeq = 1;

Is it same with your SELECT * FROM syscat.tabconst WHERE type = 'P'?
But with this sql, I can extract the actual column name for primary key.
Thanks.

[Knut Stolze]

Post by c***@hotmail.com
Knut,
Can I do something like this...?
SELECT * FROM SYSCAT.COLUMNS where KeySeq = 1;
Is it same with your SELECT * FROM syscat.tabconst WHERE type = 'P'?

No, it is not. The KEYSEQ column tells you the position of the column in
the PK of the table. If there is more than one column in the PK, you would
have to check for KEYSEQ IS NOT NULL. Besides, querying SYSCAT.TABCONST
returns the constraint while querying SYSCAT.COLUMNS returns information
about the column in the constraint - that's a different level of
information.

If you want to know the columns of a unique constraint (PKs are the same as
UNIQUE constraints), you can use the catalog view SYSCAT.KEYCOLUSE.

[c***@hotmail.com]

Thanks Knut.

Learn a lot about the syscat,
and found solution to my issue
while I developing application accessing to db2.

Thanks again.