Column Name in a Primary key constraint

[Norman Leung]

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

[Dave Hau]

Re: Column Name in a Primary key constraint

"Norman Leung" <normanl@interl og.com> wrote in message
news:3d8f97f8.0 312031321.685fd 5b7@posting.goo gle.com...[color=blue]
> 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.[/color]

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_na me, i.indid, c1.colid)
and (c.name = index_col (@full_table_na me, i.indid, 1) or
c.name = index_col (@full_table_na me, i.indid, 2) or
c.name = index_col (@full_table_na me, i.indid, 3) or
c.name = index_col (@full_table_na me, i.indid, 4) or
c.name = index_col (@full_table_na me, i.indid, 5) or
c.name = index_col (@full_table_na me, i.indid, 6) or
c.name = index_col (@full_table_na me, i.indid, 7) or
c.name = index_col (@full_table_na me, i.indid, 8) or
c.name = index_col (@full_table_na me, i.indid, 9) or
c.name = index_col (@full_table_na me, i.indid, 10) or
c.name = index_col (@full_table_na me, i.indid, 11) or
c.name = index_col (@full_table_na me, i.indid, 12) or
c.name = index_col (@full_table_na me, i.indid, 13) or
c.name = index_col (@full_table_na me, i.indid, 14) or
c.name = index_col (@full_table_na me, i.indid, 15) or
c.name = index_col (@full_table_na me, i.indid, 16)
)


[color=blue]
>
> TIA
> Norman
>[/color]

[Simon Hayes]

Re: Column Name in a Primary key constraint

normanl@interlo g.com (Norman Leung) wrote in message news:<3d8f97f8. 0312031321.685f d5b7@posting.go ogle.com>...[color=blue]
> 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[/color]

SELECT
KCU.COLUMN_NAME
FROM
INFORMATION_SCH EMA.KEY_COLUMN_ USAGE KCU
JOIN INFORMATION_SCH EMA.CONSTRAINT_ COLUMN_USAGE CCU
ON KCU.TABLE_NAME = CCU.TABLE_NAME AND
KCU.COLUMN_NAME = CCU.COLUMN_NAME

JOIN INFORMATION_SCH EMA.TABLE_CONST RAINTS TC
ON CCU.CONSTRAINT_ NAME = TC.CONSTRAINT_N AME
WHERE
KCU.TABLE_NAME = 'MyTable' AND
TC.CONSTRAINT_T YPE = 'PRIMARY KEY'

Simon