SQL Server Information_Schema

Question

I want to check a table's primary key by SQL statements, I use below SQL:

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

But I just got the constraint name but I want to see which column(s) contained in the primary key, is there a way to do that?

Answer 1

Just join against 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

Result:

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