Um in einer stored procedure per SQL den Primary-key einer Tabelle zu ermitteln muss man die beiden Tabellen INFORMATION_SCHEMA.TABLE_CONSTRAINTS und INFORMATION_SCHEMA.KEY_COLUMN_USAGE miteinander joinen:
DECLARE @tabelle varchar(128)
set @tabelle = 'Tabellenname'
SELECT KCU.Column_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE KCU.table_name=@tabelle
ORDER BY KCU.TABLE_NAME, KCU.ORDINAL_POSITION
oder ab SQL-Server 2005:
select t.name as TABLE_NAME
, k.unique_index_id
, c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
from sys.key_constraints as k
join sys.tables as t
on t.object_id = k.parent_object_id
join sys.schemas as s
on s.schema_id = t.schema_id
join sys.index_columns as ic
on ic.object_id = t.object_id
and ic.index_id = k.unique_index_id
join sys.columns as c
on c.object_id = t.object_id
and c.column_id = ic.column_id
order by TABLE_NAME, ORDINAL_POSITION
siehe: http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx
oder folgende stored procedure verwenden:
EXEC sp_Pkeys @tabelle