Getting list of columns for a PK

Question

In SQL Server 2008, I want to get the list of columns (column names) that the Primary Key spans.

I have tried

SELECT * 
FROM sys.key_constraints 
LEFT JOIN sysconstraints ON (sys.key_constraints.object_id = sysconstraints.constid)
WHERE 
    type = 'PK' 
    AND parent_object_id = OBJECT_ID('dbo.permissioncache');

This returns the primary key and some other values, but not the full list of PK columns.

What other table(s) will I have to join in?

Answer 1

Try this way

INFORMATION_SCHEMA method

SELECT TC.TABLE_NAME,
       COLUMN_NAME,
       TC.CONSTRAINT_NAME
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
       INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cc
               ON cc.Constraint_Name = tc.Constraint_Name
                  AND cc.Table_Name = tc.Table_Name
WHERE  Constraint_Type = 'PRIMARY KEY'
       AND cc.Table_Name = 'Yourtable'

Sys option,

SELECT t.name  AS TABLE_NAME,
       c.name  AS COLUMN_NAME,
       kc.name AS CONSTRAINT_NAME
FROM   sys.key_constraints AS kc
       JOIN sys.tables AS t
         ON t.object_id = kc.parent_object_id
       JOIN sys.index_columns AS ic
         ON ic.object_id = t.object_id
            AND ic.index_id = kc.unique_index_id
       JOIN sys.columns AS c
         ON c.object_id = t.object_id
            AND c.column_id = ic.column_id
WHERE  kc.type = 'PK'
       AND t.name = 'Yourtable' 

Answer 2

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

and if you want to get the list of all primary key columns in your database then

USE myDB;   
GO

SELECT i.name AS IndexName, 
       OBJECT_NAME(ic.OBJECT_ID) AS TableName, 
       COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID
       AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1