Determine a table's primary key using TSQL

Question

I'd like to determine the primary key of a table using TSQL (stored procedure or system table is fine). Is there such a mechanism in SQL Server (2005 or 2008)?

score 80 · Accepted Answer · edited Dec 04 '19 at 11:13

80

This should get you started:

SELECT 
    *
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
        ON tc.CONSTRAINT_NAME = ccu.Constraint_name
WHERE 
    tc.TABLE_NAME = 'TableName' AND 
    tc.CONSTRAINT_TYPE = 'Primary Key'

edited Dec 04 '19 at 11:13

Paul

4,160
3
30
56

answered Oct 06 '09 at 13:48

Stuart Ainsworth

12,792
41
46

`ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME AND tc.CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA` for multiple schemas – Bohdan Jan 11 '21 at 10:29

score 50 · Answer 2 · answered Oct 06 '09 at 13:49

50

How about

sp_pkeys 'TableName'

answered Oct 06 '09 at 13:49

Restore the Data Dumps

38,967
12
96
122

stellar! I'm seeing much better performance from this than from the table joins through information_schema – Mike M May 28 '15 at 19:04
1

This would be much handier if it were a table valued function that you could select from – xr280xr Aug 08 '17 at 15:22
Can't do anything with the result because it's not a function. – Emperor Eto Dec 02 '19 at 20:55
If you need a table, you can insert the results of a stored procedure into a table. First define the table `declare @PrimaryKey table (TABLE_QUALIFIER nvarchar(100), TABLE_OWNER nvarchar(100), TABLE_NAME nvarchar(100), COLUMN_NAME nvarchar(100), KEY_SEQ int, PK_NAME nvarchar(100))` and then `insert into @PrimaryKey exec sp_pkeys 'TABLE_NAME'` – Alexander Higgins Dec 02 '20 at 16:18

score 22 · Answer 3 · answered Oct 06 '09 at 13:55

Here's one based on system tables from SQL 2005 (99% sure it'd work in 2008). This will list all PKs for all user-defined tables, with all columns and some extra fluff that could be removed. Add parameters to pick out a table at a time.

SELECT
   schema_name(ta.schema_id)  SchemaName
  ,ta.name  TableName
  ,ind.name
  ,indcol.key_ordinal Ord
  ,col.name  ColumnName
  ,ind.type_desc
  ,ind.fill_factor
 from sys.tables ta
  inner join sys.indexes ind
   on ind.object_id = ta.object_id
  inner join sys.index_columns indcol
   on indcol.object_id = ta.object_id
    and indcol.index_id = ind.index_id
  inner join sys.columns col
   on col.object_id = ta.object_id
    and col.column_id = indcol.column_id
 where ind.is_primary_key = 1
 order by
   ta.name
  ,indcol.key_ordinal

score 20 · Answer 4 · answered Oct 06 '09 at 13:53

20

SELECT ccu.COLUMN_NAME, ccu.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
        ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
WHERE tc.TABLE_CATALOG = 'Your_Catalog'    -- replace with your catalog
    AND tc.TABLE_SCHEMA = 'dbo'            -- replace with your schema
    AND tc.TABLE_NAME = 'Your_Table'       -- replace with your table name
    AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'

answered Oct 06 '09 at 13:53

LukeH

263,068
57
365
409

1

This is _way_ better than the accepted answer, since it provides the table name. – Uwe Keim Oct 16 '13 at 05:07
It is also much better as it has the correct casing on the columns and PRIMARY KEY so it works on a case sensitive server! – Ed Elliott Nov 12 '14 at 15:52

score 7 · Answer 5 · edited Mar 04 '13 at 15:18

7

exec [sys].[sp_primary_keys_rowset] @table_name= 'TableName'

edited Mar 04 '13 at 15:18

answered Oct 06 '09 at 13:51

Jacob G

3,645
1
20
25

score 4 · Answer 6 · answered Oct 06 '09 at 13:49

4

EXEC sp_Pkeys @tableName

answered Oct 06 '09 at 13:49

chugh97

9,602
25
89
136

score 3 · Answer 7 · edited Dec 04 '19 at 11:16

You're better off using INFORMATION_SCHEMA.KEY_COLUMN_USAGE, as you can access the key ordering information (ORDINAL_POSITION) which is very important to know.

SELECT 
    kcu.*
FROM 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    INNER JOIN 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
        ON  tc.TABLE_NAME = kcu.TABLE_NAME AND 
            tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
ORDER BY 
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME,
    kcu.ORDINAL_POSITION

score 2 · Answer 8 · answered Dec 10 '14 at 18:25

2

The simplest way is this!

select object_id from sys.objects 
where parent_object_id = OBJECT_ID(N'FACounty')
and [type] = N'PK'

answered Dec 10 '14 at 18:25

JT Turner

502
2
14

1

This code doesn't return columns that consist primary key, try on composite primary keys. – veljasije Nov 11 '15 at 14:22

score 2 · Answer 9 · edited Nov 13 '20 at 22:26

Can't add a comment, not enough rep points, but this is in response to those saying sp_Pkeys is not usable. Doesn't have to be a function as mentioned in another comment to an answer.

DECLARE @tbl TABLE
(
  Table_Qualifier varchar(30), 
  Table_Owner varchar(30), 
  Table_Name varchar(50), 
  Column_Name varchar(30), 
  Key_Seq int,
  PK_Name varchar(50)
)

insert into @tbl EXEC sp_Pkeys 'tablename'

select * from @tbl

score 0 · Answer 10 · answered Apr 18 '16 at 10:55

0

If you already know the name of the key you're interested in, following works:

-- Assuming you have schema "Example" and the primary key name is "PK_Item"
-- Notice that name of table is irrelevant here but is "Foobar" here
IF (OBJECT_ID('Example.PK_ITEM') IS NULL)
BEGIN
    ALTER TABLE [Example].Foobar ADD CONSTRAINT
    PK_Item PRIMARY KEY ...
END

answered Apr 18 '16 at 10:55

Pasi Savolainen

2,460
1
22
35

If the OP is trying to find the primary key of a table, why would an answer that relies on knowing the primary key of the table be useful? – GreySage Jul 16 '19 at 17:20
I guess I interpreted the "determine" as "detect if it exists" – Pasi Savolainen Jul 17 '19 at 15:33

Determine a table's primary key using TSQL

10 Answers10

Linked