CODEWITHSUNDEEP
sqlsql-server-2008
user628347
user628347

Reputation: 201

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

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.

Upvotes: 6

Views: 24016

Answers (7)

Sanjeevakumar Hiremath
Sanjeevakumar Hiremath

Reputation: 11263

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'

Upvotes: 2

rmil
rmil

Reputation: 76

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.

Upvotes: 1

Fausto Odilon
Fausto Odilon

Reputation: 49

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

Upvotes: 0

Ankit
Ankit

Reputation: 2753

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.

Upvotes: 14

Jeremy Danyow
Jeremy Danyow

Reputation: 26406

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

Upvotes: 0

Anthony Geoghegan
Anthony Geoghegan

Reputation: 11983

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

Upvotes: 4

Peter Kelly
Peter Kelly

Reputation: 14391

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'

Upvotes: 1

Related Questions