Column Name in a Primary key constraint

Hi all

Would there be a easy way to find the column name(s) which constitute
a Primary constraint for a table through navigating the system
catalogs.

I found that the PK Constraint object in syscontraints is showing the
colid = 0.

TIA
Norman"Norman Leung" <normanl@.interlog.com> wrote in message
news:3d8f97f8.0312031321.685fd5b7@.posting.google.c om...
> Hi all
> Would there be a easy way to find the column name(s) which constitute
> a Primary constraint for a table through navigating the system
> catalogs.
> I found that the PK Constraint object in syscontraints is showing the
> colid = 0.

colid = 0 indicates that it's a composite PK.

To find the columns of the PK, just use the system stored procedure
"sp_pkeys", for example,

sp_pkeys sometablename

However, if you insist on using the system tables to do this, have a look at
the source code for the sp_pkeys procedure. Apparently, you need to join
the syscolumns table with the sysindexes table, filter with a 0x800 mask (I
assume this selects for a primary key index), then select for columns based
on column name using the procedure index_col().

- Dave

...
from
sysindexes i, syscolumns c, sysobjects o
where
o.id = @.table_id
and o.id = c.id
and o.id = i.id
and (i.status & 0x800) = 0x800
--and c.name = index_col (@.full_table_name, i.indid, c1.colid)
and (c.name = index_col (@.full_table_name, i.indid, 1) or
c.name = index_col (@.full_table_name, i.indid, 2) or
c.name = index_col (@.full_table_name, i.indid, 3) or
c.name = index_col (@.full_table_name, i.indid, 4) or
c.name = index_col (@.full_table_name, i.indid, 5) or
c.name = index_col (@.full_table_name, i.indid, 6) or
c.name = index_col (@.full_table_name, i.indid, 7) or
c.name = index_col (@.full_table_name, i.indid, 8) or
c.name = index_col (@.full_table_name, i.indid, 9) or
c.name = index_col (@.full_table_name, i.indid, 10) or
c.name = index_col (@.full_table_name, i.indid, 11) or
c.name = index_col (@.full_table_name, i.indid, 12) or
c.name = index_col (@.full_table_name, i.indid, 13) or
c.name = index_col (@.full_table_name, i.indid, 14) or
c.name = index_col (@.full_table_name, i.indid, 15) or
c.name = index_col (@.full_table_name, i.indid, 16)
)

> TIA
> Norman|||normanl@.interlog.com (Norman Leung) wrote in message news:<3d8f97f8.0312031321.685fd5b7@.posting.google.com>...
> Hi all
> Would there be a easy way to find the column name(s) which constitute
> a Primary constraint for a table through navigating the system
> catalogs.
> I found that the PK Constraint object in syscontraints is showing the
> colid = 0.
> TIA
> Norman

SELECT
KCU.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON KCU.TABLE_NAME = CCU.TABLE_NAME AND
KCU.COLUMN_NAME = CCU.COLUMN_NAME

JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
WHERE
KCU.TABLE_NAME = 'MyTable' AND
TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

Simon