如何列出 SQL Server 表的主键？

How do you list the primary key of a SQL Server table?

提问by swilliams

Simple question, how do you list the primary key of a table with T-SQL? I know how to get indexes on a table, but can't remember how to get the PK.

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

回答by Guy Starbuck

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 = '<your table name>'

回答by Dave Zych

It's generally recommended practice now to use the sys.*views over INFORMATION_SCHEMAin SQL Server, so unless you're planning on migrating databases I would use those. Here's how you would do it with the sys.*views:

现在通常推荐的做法是在 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('<schema>.<tablename>');

回答by SQL Police

This is a solution which uses only sys-tables.

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

It lists all the primary keys in the database. It returns schema, table name, column nameand the correct column sort orderfor each primary key.

它列出了数据库中的所有主键。它为每个主键返回架构、表名、列名和正确的列排序顺序。

If you want to get the primary key for a specific table, then you need to filter on SchemaNameand TableName.

如果要获取特定表的主键，则需要对SchemaName和进行过滤TableName。

IMHO, this solution is very generic and does not use any string literals, so it will run on any machine.

恕我直言，这个解决方案非常通用，不使用任何字符串文字，所以它可以在任何机器上运行。

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 ;

回答by KyleMit

Here's another way from the question get table primary key using sql query:

这是使用 sql 查询获取表主键问题的另一种方法：

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

It uses KEY_COLUMN_USAGEto determine the constraints for a given table
Then uses OBJECTPROPERTY(id, 'IsPrimaryKey')to determine if each is a primary key

它用于KEY_COLUMN_USAGE确定给定表的约束
然后用于确定每个表是否为主键OBJECTPROPERTY(id, 'IsPrimaryKey')

回答by Dwight T

Is using MS SQL Server you can do the following:

使用 MS SQL Server 您可以执行以下操作：

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

You can also filter on the table_name column if you want a specific table.

如果需要特定表，还可以对 table_name 列进行过滤。

回答by user12861

I like the INFORMATION_SCHEMA technique, but another I've used is: exec sp_pkeys 'table'

我喜欢 INFORMATION_SCHEMA 技术，但我使用的另一个技术是：exec sp_pkeys 'table'

回答by Manjunath C Bhat

--This is another Modified Version which is also an example for Co-Related Query

--这是另一个修改版本，也是相关查询的示例

SELECT TC.TABLE_NAME as [Table_name], TC.CONSTRAINT_NAME as [Primary_Key]
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
 INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
 ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
 WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
 TC.TABLE_NAME IN
 (SELECT [NAME] AS [TABLE_NAME] FROM SYS.OBJECTS 
 WHERE TYPE = 'U')

回答by dekdev

This should list all the constraints ( primary Key and Foreign Keys ) and at the end of query put table name

这应该列出所有约束（主键和外键）并在查询结束时放置表名

/* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/
WITH   ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME) 
AS
(
SELECT  CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) ,
        CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) ,
        PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) ,
        PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) ,
        PARENT_COL_NAME_DATA_TYPE=  oParentColDtl.DATA_TYPE,        
        REFERENCE_TABLE_NAME='' ,
        REFERENCE_COL_NAME='' 

FROM sys.key_constraints as PKnUKEY
    INNER JOIN sys.tables as PKnUTable
            ON PKnUTable.object_id = PKnUKEY.parent_object_id
    INNER JOIN sys.index_columns as PKnUColIdx
            ON PKnUColIdx.object_id = PKnUTable.object_id
            AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
    INNER JOIN sys.columns as PKnUKEYCol
            ON PKnUKEYCol.object_id = PKnUTable.object_id
            AND PKnUKEYCol.column_id = PKnUColIdx.column_id
     INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
            ON oParentColDtl.TABLE_NAME=PKnUTable.name
            AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
UNION ALL
SELECT  CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) ,
        CONSTRAINT_TYPE='FK',
        PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) ,
        PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) ,
        PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,     
        REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) ,
        REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30)) 
FROM sys.foreign_key_columns FKC
    INNER JOIN sys.sysobjects oConstraint
            ON FKC.constraint_object_id=oConstraint.id 
    INNER JOIN sys.sysobjects oParent
            ON FKC.parent_object_id=oParent.id
    INNER JOIN sys.all_columns oParentCol
            ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/
            AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
    INNER JOIN sys.sysobjects oReference
            ON FKC.referenced_object_id=oReference.id
    INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
            ON oParentColDtl.TABLE_NAME=oParent.name
            AND oParentColDtl.COLUMN_NAME=oParentCol.name
    INNER JOIN sys.all_columns oReferenceCol
            ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/
            AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/

)

select * from   ALL_KEYS_IN_TABLE
where   
    PARENT_TABLE_NAME  in ('YOUR_TABLE_NAME') 
    or REFERENCE_TABLE_NAME  in ('YOUR_TABLE_NAME')
ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME;

For reference please read thru - http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx

如需参考，请通读 - http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx

回答by MartinC

Thanks Guy.

哥们，谢啦。

With a slight variation I used it to find all the primary keys for all the tables.

稍有不同，我用它来查找所有表的所有主键。

SELECT A.Name,Col.Column_Name from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col ,
    (select NAME from dbo.sysobjects where xtype='u') AS A
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY '
    AND Col.Table_Name = A.Name

回答by Manjunath C Bhat

SELECT A.TABLE_NAME as [Table_name], A.CONSTRAINT_NAME as [Primary_Key]
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
 WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME