SQL Server：使用sql查询获取表主键

Question

我想使用针对SQL Server数据库的SQL查询来获取特定表的主键。在MySQL中，我使用以下查询来获取表主键：SHOW KEYS FROM tablename WHERE Key_name = 'PRIMARY'什么是上述SQL Server查询的等效项？如果存在适用于MySQL和SQL Server的查询，则将是理想的情况。

Answer 1

找到了另一个：

SELECT KU.table_name as TABLENAME,column_name as PRIMARYKEYCOLUMN

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC

INNER JOIN

    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU

          ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND

             TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND 

             KU.table_name='yourTableName'

ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;

我已经在SQL Server 2003/2005上对此进行了测试

Answer 2

从内存来看，要么是

SELECT * FROM sys.objects

WHERE type = 'PK' 

AND  object_id = OBJECT_ID ('tableName')

或这个..

SELECT * FROM sys.objects

WHERE type = 'PK' 

AND  parent_object_id = OBJECT_ID ('tableName')

我认为