How can I select primary key columns from a table?

Question

I need to select columns that are a primary key or a column that is not null. How can i do this?

And I want only columns, not values.

Answer 1

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

Answer 2

To list the columns of the primary key, 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 list 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 3

Primary Key Column List

To list the primary key columns, I used SQL Server to implement the standard ANSI standard , Information object_name() Details , because they are easier to work with: theres no, you must use the object_name() function to translate object_id into human-friendly names.

I use [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] to list the restrictions on the table — both primary and foreign keys; [INFORMATION_SCHEMA].CONSTRAINT_COLUMN_USAGE has similar information, but ORDINAL_POSITION missing.

[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS] contains additional information about (most importantly CONSTRAINT_TYPE ), but does not list the columns to which the restriction applies.

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

 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 

Answer 4

You can use the 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' 

Answer 5

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

This example is tested on mariadb, mysql. May work on others, but depends on details of information_schema.

Answer 6

using only 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 

Answer 7

I found the solution on its own:

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