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

I need to sele开发者_运维百科ct 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.

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.

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

Next answer is more apropriate, I'm not sure how to transfer accepted answer.

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = '<TABLE_NAME>' and IS_NULLABLE = 'NO'

You could use a built-in System View called INFORMATION_KEY_COLUMN_USAGE to get the primary key columns

SELECT [COLUMN_NAME]
FROM [DatabaseName].[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]
WHERE [TABLE_NAME] = 'TableName'

SELECT * FROM INFORMATION_SCHEMA.COLUMNS  
WHERE TABLE_NAME = '<your_table>' and COLUMN_KEY = 'PRI'

this example is tested on mariadb, mysql. Might work on others but depends on information_schema details.

using only the sys.* tables:

select
    t.name,
    kc.type,
    kc.name,
    c.name,
    i.is_unique,
    i.is_primary_key,
    i.is_unique_constraint,
    ic.is_descending_key,
    ic.key_ordinal,
    ic.is_included_column
from sys.key_constraints kc
inner join sys.objects t on t.object_id = kc.parent_object_id
inner join sys.indexes i on i.name = kc.name
inner join sys.index_columns ic on ic.object_id = kc.parent_object_id and ic.index_id = i.index_id
inner join sys.columns c on c.object_id = kc.parent_object_id and c.column_id = ic.column_id
order by
    t.name,
    kc.type,
    kc.name,
    ic.key_ordinal

I found a solution by myself after all:

select sc.name from sys.objects as so
inner join sys.indexes as si        on so.object_id = si.object_id 
                                    and si.is_primary_key = 1
inner join sys.index_columns as ic  on si.object_id = ic.object_id
                                    and si.index_id = ic.index_id
inner join sys.columns as sc            on so.object_id = sc.object_id
                                    and ic.column_id = sc.column_id
where so.object_id = object_id('TABLE_NAME')