如何列出SQL Server表的主键？

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

SELECT Col.Column_Name from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY'
    AND Col.Table_Name = ''

现在通常建议在SQL Server中使用sys.*视图INFORMATION_SCHEMA,因此除非您计划迁移数据库,否则我会使用它们.以下是您对sys.*视图的处理方式:

SELECT 
    c.name AS column_name,
    i.name AS index_name,
    c.is_identity
FROM sys.indexes i
    inner join sys.index_columns ic  ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    inner join sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
    and i.object_ID = OBJECT_ID('.');

这是一个仅使用sys -tables 的解决方案.

它列出了数据库中的所有主键.它返回每个主键的模式,表名,列名和正确的列排序顺序.

如果要获取特定表的主键,则需要过滤SchemaName和TableName.

恕我直言,这个解决方案非常通用,不使用任何字符串文字,因此它将在任何机器上运行.

select 
    s.name as SchemaName,
    t.name as TableName,
    tc.name as ColumnName,
    ic.key_ordinal as KeyOrderNr
from 
    sys.schemas s 
    inner join sys.tables t   on s.schema_id=t.schema_id
    inner join sys.indexes i  on t.object_id=i.object_id
    inner join sys.index_columns ic on i.object_id=ic.object_id 
                                   and i.index_id=ic.index_id
    inner join sys.columns tc on ic.object_id=tc.object_id 
                             and ic.column_id=tc.column_id
where i.is_primary_key=1 
order by t.name, ic.key_ordinal ;

以下是使用sql查询获取表主键的另一种方法:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA+'.'+CONSTRAINT_NAME), 'IsPrimaryKey') = 1
  AND TABLE_NAME = ''

--List all tables primary keys
select * from information_schema.table_constraints
where constraint_type = 'Primary Key'