Here is the code to get the PK of a table.
CREATE PROCEDURE GetTablePK(
@TableName Varchar(20) = NULL
)
AS
BEGIN
select [name] from syscolumns where
[id] in (select [id]
from sysobjects
where [name] = @TableName) and
colid in (select SIK.colid
from
sysindexkeys SIK join sysobjects SO on
SIK.[id] = SO.[id]
where
SIK.indid = 1 and
SO.[name] = @TableName)
END
@TableName Varchar(20) = NULL
)
AS
BEGIN
select [name] from syscolumns where
[id] in (select [id]
from sysobjects
where [name] = @TableName) and
colid in (select SIK.colid
from
sysindexkeys SIK join sysobjects SO on
SIK.[id] = SO.[id]
where
SIK.indid = 1 and
SO.[name] = @TableName)
END
we use the system table sys.sysindexkeys that contains information about the keys or columns in an index of the database.