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

解决方案

系统存储过程" sp_help"将为我们提供信息。执行以下语句：

execute sp_help table_name

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>'

使用MS SQL Server，我们可以执行以下操作：

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

如果需要特定的表，也可以在table_name列上进行过滤。

试试看：

SELECT
    CONSTRAINT_CATALOG AS DataBaseName,
    CONSTRAINT_SCHEMA AS SchemaName,
    TABLE_NAME AS TableName,
    CONSTRAINT_Name AS PrimaryKey
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_TYPE = 'Primary Key' and Table_Name = 'YourTable'

SELECT t.name AS 'table', i.name AS 'index', it.xtype,

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 1 
        AND k.id = t.id)
    AS 'column1',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 2 
        AND k.id = t.id)
    AS 'column2',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 3
        AND k.id = t.id)
    AS 'column3',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 4
        AND k.id = t.id)
    AS 'column4',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 5
        AND k.id = t.id)
    AS 'column5',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 6
        AND k.id = t.id)
    AS 'column6',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 7
        AND k.id = t.id)
    AS 'column7',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 8 
        AND k.id = t.id)
    AS 'column8',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 9 
        AND k.id = t.id)
    AS 'column9',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 10
        AND k.id = t.id)
    AS 'column10',

FROM sysobjects t
    INNER JOIN sysindexes i ON i.id = t.id 
    INNER JOIN sysobjects it ON it.parent_obj = t.id AND it.name = i.name

WHERE it.xtype = 'PK'
ORDER BY t.name, i.name

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

谢了，兄弟们。

略有变化，我用它来查找所有表的所有主键。

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