search the primary key given the table name

[Robert Song]

Hi all,

How can get the primary key string from the given table name? i know
it should from system tables of "sysobjects, syscolumns, and
sysconstraints", but when i execute the statement like that:

select a.name from syscolumns a,sysobjects b,sysconstraints c
where a.id = b.id and b.name ='Agreement' and a.id = c.id and a.colid
= c.colid and c.status = 1

i can't get the primary key out, what the trick here? bye the sql help
file,
'status' in sysconstraints table:
1 = PRIMARY KEY constraint.
2 = UNIQUE KEY constraint.

what is exact value refers to PRIMARY KEY constraint?

thanks,

Robert

[Erland Sommarskog]

Robert Song (ro*********@mda-software.com) writes:

How can get the primary key string from the given table name? i know
it should from system tables of "sysobjects, syscolumns, and
sysconstraints", but when i execute the statement like that:

select a.name from syscolumns a,sysobjects b,sysconstraints c
where a.id = b.id and b.name ='Agreement' and a.id = c.id and a.colid
= c.colid and c.status = 1

i can't get the primary key out, what the trick here? bye the sql help

The trick is that sysconstraints is a dead end. colid only applies
for column constraints, and a PK is a table constraint; at least a
multi-colummn constraint is.

This query should give you what you want:

use Northwind
go
select c.name
from sysindexes i
join sysobjects o ON i.id = o.id
join sysobjects pk ON i.name = pk.name
AND pk.parent_obj = i.id
AND pk.xtype = 'PK'
join sysindexkeys ik on i.id = ik.id
and i.indid = ik.indid
join syscolumns c ON ik.id = c.id
AND ik.colid = c.colid
where o.name = 'Order Details'
order by ik.keyno
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp