How can I select the primary key columns from a table?

Question

I need to select the columns which is the primary key or the column which is not null. How can I do that?

And I want only the columns, not the values.

Answer 1

To list the primary key columns, you can try this query:

SELECT
    kc.name,
    c.NAME
FROM 
    sys.key_constraints kc
INNER JOIN 
    sys.index_columns ic ON kc.parent_object_id = ic.object_id  and kc.unique_index_id = ic.index_id
INNER JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE
    kc.type = 'PK'

and to list the foreign keys, use the following:

SELECT
    OBJECT_NAME(parent_object_id) 'Parent table',
    c.NAME 'Parent column name',
    OBJECT_NAME(referenced_object_id) 'Referenced table',
    cref.NAME 'Referenced column name'
FROM 
    sys.foreign_key_columns fkc
INNER JOIN 
    sys.columns c 
       ON fkc.parent_column_id = c.column_id 
          AND fkc.parent_object_id = c.object_id
INNER JOIN 
    sys.columns cref 
       ON fkc.referenced_column_id = cref.column_id 
          AND fkc.referenced_object_id = cref.object_id

Hope this helps.

Answer 2

List Primary Key Columns

To list the Primary Key columns, I used SQL Server’s implementation of the ANSI standard Information Schema Views as they’re easier to work with: there’s no need to use the object_name() function to translate object_ids to human-readable names.

I use [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] to list the constraints on a table – both primary and foreign keys; [INFORMATION_SCHEMA].CONSTRAINT_COLUMN_USAGE has similar information but lacks the ORDINAL_POSITION.

[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] provides extra information on the constraints (most importantly the CONSTRAINT_TYPE) but doesn’t list the columns that the constraint applies to.

To get the only the list of columns used by the primary key, join the above two tables using the name of the constraint:

SELECT
     tc.TABLE_SCHEMA
    ,tc.TABLE_NAME
    ,tc.CONSTRAINT_NAME
    ,kcu.COLUMN_NAME
    ,kcu.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
    ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME

WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND tc.TABLE_NAME = @TableName