SQL Server Information_Schema

Question

我想通过SQL语句检查表的主键，我使用下面的SQL：

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
AND TABLE_NAME = 'T_ODSIN_DRG_BP_ROLE_DET'

但是我只是得到了约束名称，但是我想看看主键中包含哪些列，有什么方法可以做到这一点吗？

Answer 1

只需加入INFORMATION_SCHEMA.KEY_COLUMN_USAGE即可

IF OBJECT_ID('dbo.TestPKTable') IS NOT NULL
    DROP TABLE dbo.TestPKTable

CREATE TABLE dbo.TestPKTable (
    FirstPKColumn INT, 
    SecondPKColumn INT, 
    CONSTRAINT PK_TestPKTable PRIMARY KEY (FirstPKColumn, SecondPKColumn))

SELECT 
    PK.CONSTRAINT_NAME,
    PK.TABLE_CATALOG,
    PK.TABLE_SCHEMA,
    PK.TABLE_NAME,
    CU.COLUMN_NAME,
    CU.ORDINAL_POSITION
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS PK
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON 
        PK.CONSTRAINT_NAME = CU.CONSTRAINT_NAME AND
        PK.CONSTRAINT_CATALOG = CU.CONSTRAINT_CATALOG AND
        PK.CONSTRAINT_SCHEMA = CU.CONSTRAINT_SCHEMA
WHERE
    PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
    PK.CONSTRAINT_NAME,
    CU.ORDINAL_POSITION

结果：

CONSTRAINT_NAME     TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME  COLUMN_NAME     ORDINAL_POSITION
PK_TestPKTable      TestDB          dbo             TestPKTable FirstPKColumn   1
PK_TestPKTable      TestDB          dbo             TestPKTable SecondPKColumn  2