How do I determine if a column is in the primary key of its table? (SQL Server)

Question

I am currently using...

select Table_Name, Column_name, data_type, is_Nullable from information_Schema.Columns 

...to determine information about columns in a given database for the purposes of generating a DataAccess Layer.

From where can I retrieve information about if these columns are participants in the primary key of their table?

Answer 1

Here is one way (replace 'keycol' with the column name you are searching for):

SELECT  K.TABLE_NAME ,     K.COLUMN_NAME ,     K.CONSTRAINT_NAME FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS C         JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS K ON C.TABLE_NAME = K.TABLE_NAME                                                          AND C.CONSTRAINT_CATALOG = K.CONSTRAINT_CATALOG                                                          AND C.CONSTRAINT_SCHEMA = K.CONSTRAINT_SCHEMA                                                          AND C.CONSTRAINT_NAME = K.CONSTRAINT_NAME WHERE   C.CONSTRAINT_TYPE = 'PRIMARY KEY'         AND K.COLUMN_NAME = 'keycol';