Getting a list of columns for PC

Question

In SQL Server 2008, I want to get a list of columns (column names) in which the primary key is distributed.

I 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'); 

Returns the primary key and some other values, but not a complete list of PK columns.

What other tables will I need to join?

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

 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 a list of all the 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 

Answer 3

use the inner join without leaving the outer join, if you use the left outer join, it will give all the rows from the left table.