SQL Server: Get table primary key using sql query

Question

I want to get a particular table's primary key using SQL query for SQL Server database.

In MySQL I am using following query to get table primary key:

SHOW KEYS FROM tablename WHERE Key_name = 'PRIMARY'

What is equivalent of above query for SQL Server ?.

If There is a query that will work for both MySQL and SQL Server then It will be an ideal case.

Be careful when using INFORMATION_SCHEMA.Views because they can not reliably return CONSTRAINT_SCHEMA and TABLE_SCHEMA (see, for example: http://msdn.microsoft.com/en-us/library/ms181757.aspx) For discussion about this problem check this MSDN thread http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/732bd071-2c1f-4c23-9215-4ff3822c63c3 — Naomi, Nov 30 '12 at 01:37
Select distinct SUBSTRING ( stuff(( select distinct ',' + [COLUMN_NAME] from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1 AND TABLE_NAME = 'TableName' AND TABLE_SCHEMA = 'Schema' order by 1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,0,'' ) ,2,9999) — Allan F, Aug 26 '19 at 01:02

score 185 · Answer 1 · edited Feb 01 '17 at 14:57

185

I also found another one for SQL Server:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
AND TABLE_NAME = 'TableName' AND TABLE_SCHEMA = 'Schema'

edited Feb 01 '17 at 14:57

JodyT

4,324
2
19
31

answered Oct 14 '10 at 06:47

Awan

18,096
36
89
131

1

It is not running on SQL Server 2005. I added another answer which is useful for both SQL Server 2003 and SQL Server 2005. – Awan Oct 15 '10 at 14:02
2

Don't know why, but OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') returns NULL in my case. The solution below works fine. Maybe your solution doesn't work for complex PKs (2 and more columns). – nZeus Mar 12 '14 at 10:31
3

If you are suffering from the same issue I was, The object property function is returning null because the object id function will return null if the constraint is attached to an object not in the dbo schema. You have to concatenate the constraint schema to the constraint name inside of the object id function. Hope this helps someone else. – jwhaley58 Mar 26 '14 at 15:10
@MgSam it's not working for newer SQL Server versions. – Roger Far Jun 03 '16 at 16:15
Also it is not working for constraint names contaning dot and space symbols. Should be `OBJECT_ID(CONSTRAINT_SCHEMA + '.[' + CONSTRAINT_NAME + ']')` – Ruslan F. Feb 01 '17 at 14:37

score 106 · Accepted Answer · edited Jul 09 '20 at 13:08

106

Found another one:

SELECT 
     KU.table_name as TABLENAME
    ,column_name as PRIMARYKEYCOLUMN
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC 

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
    ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' 
    AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME 
    AND KU.table_name='YourTableName'

ORDER BY 
     KU.TABLE_NAME
    ,KU.ORDINAL_POSITION
;

I have tested this on SQL Server 2003/2005

edited Jul 09 '20 at 13:08

Stefan Steiger

78,642
66
377
442

answered Oct 15 '10 at 14:01

Awan

18,096
36
89
131

7

What's important about this one is it gives you the PK fields **in order**. Sometimes it matters! – Kip Jan 31 '13 at 22:00
Another version that puts them in order in a comma separated list is here: http://stackoverflow.com/a/42985271/1339704 – Soenhay Mar 23 '17 at 20:00
2

Using `information_schema` (in contrast to the `sys.` views) is always a good idea as it is the official standard and is implemented in a couple other database systems. – jakubiszon Nov 04 '17 at 17:49
Maybe it should be used with SELECT DISTINCT as it returns tens of the same result. – Guney Ozsan Oct 15 '18 at 01:13
Works on SQL CE 4.0 too! – Richard Petheram May 24 '19 at 19:29

score 67 · Answer 3 · answered Oct 14 '10 at 05:39

67

Using SQL SERVER 2005, you can try

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

Found at SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

answered Oct 14 '10 at 05:39

Adriaan Stander

162,879
31
289
284

Where will place my desired table name(Primary key required for ) in above query ? – Awan Oct 15 '10 at 14:08
Very nice because it displays them in order of occurance instead of A-Z – Jacob Goulden Apr 24 '14 at 11:59
This query ran a lot faster than the most upvoted answer on my small and empty database. – Gobe Feb 13 '16 at 20:20

score 12 · Answer 4 · edited May 28 '13 at 08:49

12

From memory, it's either this

SELECT * FROM sys.objects
WHERE type = 'PK' 
AND  object_id = OBJECT_ID ('tableName')

or this..

SELECT * FROM sys.objects
WHERE type = 'PK' 
AND  parent_object_id = OBJECT_ID ('tableName')

I think one of them should probably work depending on how the data is stored but I am afraid I have no access to SQL to actually verify the same.

edited May 28 '13 at 08:49

Philipp M

1,877
7
27
38

answered Oct 14 '10 at 05:35

Jagmag

10,283
1
34
58

This returns some information including the PK constraint name, but it doesn't return the column name unfortunately. – BrianLegg Feb 06 '19 at 21:17

score 11 · Answer 5 · answered Jun 10 '12 at 07:06

11

SELECT COLUMN_NAME FROM {DATABASENAME}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME LIKE '{TABLENAME}' AND CONSTRAINT_NAME LIKE 'PK%'

WHERE
{DATABASENAME} = your database from your server AND
{TABLENAME} = your table name from which you want to see the primary key.

NOTE : enter your database name and table name without brackets.

answered Jun 10 '12 at 07:06

Waqas Ghouri

1,079
2
16
36

1

Note: that you would absolutely have to name your constraint with "PK". I mean that is the convention, but in the real world it's not always the case. But thumbs up. – Jason Foglia Jun 27 '18 at 16:37
I would also add TABLE_SCHEMA in where filter for unambiguity. – Triamus Aug 06 '20 at 14:31

score 5 · Answer 6 · edited Oct 14 '10 at 06:04

5

select * 
from sysobjects 
where xtype='pk' and 
   parent_obj in (select id from sysobjects where name='tablename')

this will work in sql 2005

edited Oct 14 '10 at 06:04

marc_s

732,580
175
1,330
1,459

answered Oct 14 '10 at 05:42

Harendra

249
3
10

If you post code or XML, **please** highlight those lines in the text editor and click on the "code" button (101 010) on the editor toolbar to nicely format and syntax highlight it! – marc_s Oct 14 '10 at 06:04
1

Also, in SQL Server 2005 and up, it's recommended to use the `sys` catalog views and stop using the legacy `sysobjects` table. So in your case, use `sys.tables` and `sys.columns` and other sys catalog views. – marc_s Oct 14 '10 at 06:05

score 4 · Answer 7 · answered Oct 14 '10 at 05:46

The code I'll give you works and retrieves not only keys, but a lot of data from a table in SQL Server. Is tested in SQL Server 2k5/2k8, dunno about 2k. Enjoy!

SELECT DISTINCT
    sys.tables.object_id AS TableId,
    sys.columns.column_id AS ColumnId,
    sys.columns.name AS ColumnName,
    sys.types.name AS TypeName,
    sys.columns.precision AS NumericPrecision,
    sys.columns.scale AS NumericScale,
    sys.columns.is_nullable AS IsNullable,
    (   SELECT 
            COUNT(column_name)
        FROM 
            INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE 
        WHERE
            TABLE_NAME = sys.tables.name AND
            CONSTRAINT_NAME =
                (   SELECT
                    constraint_name
                    FROM 
                        INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                    WHERE
                        TABLE_NAME = sys.tables.name AND                    
                        constraint_type = 'PRIMARY KEY' AND
                        COLUMN_NAME = sys.columns.name
                )
    ) AS IsPrimaryKey,
    sys.columns.max_length / 2 AS CharMaxLength /*BUG*/
FROM 
    sys.columns, sys.types, sys.tables 
WHERE
    sys.tables.object_id = sys.columns.object_id AND
    sys.types.system_type_id = sys.columns.system_type_id AND
    sys.types.user_type_id = sys.columns.user_type_id AND
    sys.tables.name = 'TABLE'
ORDER BY 
    IsPrimaryKey

You can use only the primary key part, but I think that the rest might become handy. Best regards, David

aked · Answer 8 · 2012-01-20T17:28:11.097

This should list all the constraints and at the end you can put your filters

/* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/
WITH   ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME) 
AS
(
SELECT  CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) ,
        CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) ,
        PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) ,
        PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) ,
        PARENT_COL_NAME_DATA_TYPE=  oParentColDtl.DATA_TYPE,        
        REFERENCE_TABLE_NAME='' ,
        REFERENCE_COL_NAME='' 

FROM sys.key_constraints as PKnUKEY
    INNER JOIN sys.tables as PKnUTable
            ON PKnUTable.object_id = PKnUKEY.parent_object_id
    INNER JOIN sys.index_columns as PKnUColIdx
            ON PKnUColIdx.object_id = PKnUTable.object_id
            AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
    INNER JOIN sys.columns as PKnUKEYCol
            ON PKnUKEYCol.object_id = PKnUTable.object_id
            AND PKnUKEYCol.column_id = PKnUColIdx.column_id
     INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
            ON oParentColDtl.TABLE_NAME=PKnUTable.name
            AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
UNION ALL
SELECT  CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) ,
        CONSTRAINT_TYPE='FK',
        PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) ,
        PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) ,
        PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,     
        REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) ,
        REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30)) 
FROM sys.foreign_key_columns FKC
    INNER JOIN sys.sysobjects oConstraint
            ON FKC.constraint_object_id=oConstraint.id 
    INNER JOIN sys.sysobjects oParent
            ON FKC.parent_object_id=oParent.id
    INNER JOIN sys.all_columns oParentCol
            ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/
            AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
    INNER JOIN sys.sysobjects oReference
            ON FKC.referenced_object_id=oReference.id
    INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
            ON oParentColDtl.TABLE_NAME=oParent.name
            AND oParentColDtl.COLUMN_NAME=oParentCol.name
    INNER JOIN sys.all_columns oReferenceCol
            ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/
            AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/

)

select * from   ALL_KEYS_IN_TABLE
where   
    PARENT_TABLE_NAME  in ('YOUR_TABLE_NAME') 
    or REFERENCE_TABLE_NAME  in ('YOUR_TABLE_NAME')
ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME;

For reference please read thru - http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx

score 3 · Answer 9 · edited Jan 30 '17 at 13:00

3

Keep in mind that if you want to get exact primary field you need to put TABLE_NAME and TABLE_SCHEMA into the condition.

this solution should work:

select COLUMN_NAME from information_schema.KEY_COLUMN_USAGE 
where CONSTRAINT_NAME='PRIMARY' AND TABLE_NAME='TABLENAME' 
AND TABLE_SCHEMA='DATABASENAME'

edited Jan 30 '17 at 13:00

Shakeer Mirza

5,054
2
18
41

answered Sep 21 '12 at 11:54

Rathienth Baskaran

795
7
5

4

The CONSTRAINT_NAME is not always 'PRIMARY' – frostymarvelous Jun 27 '15 at 12:24

score 1 · Answer 10 · answered Oct 14 '10 at 07:07

It is also (Transact-SQL) ... according to BOL.

-- exec sp_serveroption 'SERVER NAME', 'data access', 'true' --execute once  

EXEC sp_primarykeys @table_server = N'server_name', 
  @table_name = N'table_name',
  @table_catalog = N'db_name', 
  @table_schema = N'schema_name'; --frequently 'dbo'

SQL Server: Get table primary key using sql query

10 Answers10

Linked

Related