list all table primary key

Hi All,

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

calvinscy-at-hotmail.com wrote:

> 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'

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

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.

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.

calvinscy-at-hotmail.com wrote:

> 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.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

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

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

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

calvinscy-at-hotmail.com wrote:
> 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.

Thanks Knut.

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

Thanks again.

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.