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

Solution 1

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.

Solution 2

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

Solution 3

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

select * from sys.key_constraints;

Solution 4

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'

View more solutions

Comments

[nam]

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.

[adolf garlic]

why is this the answer, it only does it for a table not a database, which was the original point of the question

[Michael]

The link referenced here is invalid

[Cambesa]

However you can get all tables used in the database and loop over those tables to get all primary keys

[Kevon]

Brilliant! I spent an hour trying to find an answer which would allow me to find all of the PKs which are NOT int