Get a List of all Primary Keys in a Database

Question

Is this the best way to - Get a List of all Primary Keys in a Database - or is there something better?

SELECT
KCU.TABLE_NAME AS Table_Name,
KCU.CONSTRAINT_NAME AS Constraint_Name,
KCU.COLUMN_NAME AS COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON KCU.CONSTRAINT_SCHEMA = TC.CONSTRAINT_SCHEMA
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND KCU.TABLE_SCHEMA = TC.TABLE_SCHEMA
AND KCU.TABLE_NAME = TC.TABLE_NAME
WHERE
TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
KCU.TABLE_SCHEMA, KCU.TABLE_NAME, KCU.CONSTRAINT_NAME

Please choose a better title. Also what do you mean by "best"? — Martin Smith, May 24 '11 at 10:00
You could probably also use @@Identity but i dunno if that's anywhere near what you mean? — Ruben, May 24 '11 at 10:02
@cularis - RE: Your proposed tag edit `INFORMATION_SCHEMA` does definitely not imply `MySQL`. These are meant to be portable views. — Martin Smith, May 24 '11 at 10:05
Duplicate post : check this out http://stackoverflow.com/questions/3930338/sql-server-get-table-primary-key-using-sql-query — vignesh, Jan 29 '15 at 12:50

score 46 · Answer 1 · edited May 24 '11 at 11:37

46

USE databasename; 

GO

SELECT i.name AS IndexName, OBJECT_NAME(ic.OBJECT_ID) AS TableName, 
       COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

This query will extract the all primary key constraints from the database... u just need to execute this query and type the database name in first line

edited May 24 '11 at 11:37

Sathyajith Bhat

21,321
22
95
134

answered May 24 '11 at 11:19

Kapil

504
3
4

Thank you for this. I have a LOT of tables and needed the keys for all tables to use in a new table, and this was the code I needed. Thank you. – Wayne Barron Mar 13 '23 at 01:53

score 12 · Answer 2 · answered May 30 '15 at 20:37

12

The following syntax give you all constraints in database in use.

select * from sys.key_constraints;

answered May 30 '15 at 20:37

Ashraf Sada

4,527
2
44
48

7

To add to a response from @Ashraf, you can use `select OBJECT_NAME(parent_object_id), * from sys.key_constraints where type = 'PK'`. This will give you the table name and their primary key names for the tables that have a primary key. – nam Oct 26 '15 at 16:22

score 9 · Answer 3 · answered Aug 23 '17 at 14:51

If you want the data type information as well:

SELECT 
    so.name 'Table Name',
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
INNER JOIN 
    sysobjects so ON c.object_id = so.id
WHERE
    i.is_primary_key = 1 and 
    so.xtype = 'U'
Order By 'Table Name', 'Column Name'

Brilliant! I spent an hour trying to find an answer which would allow me to find all of the PKs which are NOT int — Kevon, Dec 30 '19 at 19:32

score 6 · Accepted Answer · answered May 24 '11 at 10:03

6

look on link

EXEC sp_pkeys '<tablename>'
EXEC sp_helpconstraint '<tablename>'

sp_pkeys will return a row for each column that participates in the primary key for . The columns you are likely most interested in are COLUMN_NAME and PK_NAME.

sp_helpconstraint will list all constraints for , including foreign keys that reference the table. In the first recordset, there will only be a column called Object Name (kind of useless, since that's what you passed in). In the second resultset, there will be the following columns: constraint_type, constraint_name, and constraint_keys.

answered May 24 '11 at 10:03

Haim Evgi

123,187
45
217
223

8

why is this the answer, it only does it for a table not a database, which was the original point of the question – adolf garlic Oct 05 '16 at 15:11
2

The link referenced here is invalid – Michael May 19 '17 at 16:29
However you can get all tables used in the database and loop over those tables to get all primary keys – Cambesa Jan 11 '19 at 11:29

score 0 · Answer 5 · answered Dec 18 '14 at 11:52

0

If you are doing this from java you can also use the getPrimaryKeys method in the databasemetadata object. Perhaps other languages have similar ways to do it.

answered Dec 18 '14 at 11:52

dan b

1,172
8
20

Get a List of all Primary Keys in a Database

5 Answers5

Linked