如何列出SQL Server表的主键？

Question

一个简单的问题，如何使用T-SQL列出表的主键？我知道如何获取表上的索引，但不记得如何获取PK。

Answer 1

SELECT Col.Column_Name from&nbsp;&nbsp; &nbsp; INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,&nbsp;&nbsp; &nbsp; INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col&nbsp;WHERE&nbsp;&nbsp; &nbsp; Col.Constraint_Name = Tab.Constraint_Name&nbsp; &nbsp; AND Col.Table_Name = Tab.Table_Name&nbsp; &nbsp; AND Constraint_Type = 'PRIMARY KEY'&nbsp; &nbsp; AND Col.Table_Name = '<your table name>'

Answer 2

现在通常建议您在SQL Server中使用sys.*视图INFORMATION_SCHEMA，因此，除非您打算迁移数据库，否则我将使用这些视图。使用sys.*视图的方法如下：SELECT&nbsp;&nbsp; &nbsp; c.name AS column_name,&nbsp; &nbsp; i.name AS index_name,&nbsp; &nbsp; c.is_identityFROM sys.indexes i&nbsp; &nbsp; inner join sys.index_columns ic&nbsp; ON i.object_id = ic.object_id AND i.index_id = ic.index_id&nbsp; &nbsp; inner join sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_idWHERE i.is_primary_key = 1&nbsp; &nbsp; and i.object_ID = OBJECT_ID('<schema>.<tablename>');