How do you list the primary key of a SQL Server table?

Question

Simple question, how do you list the primary key of a table with T-SQL? I know how to get indexes on a table, but can't remember how to get the PK.

score 181 · Accepted Answer · edited Nov 29 '22 at 18:38

181

SELECT Col.Column_Name from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Tab.Constraint_Type = 'PRIMARY KEY'
    AND Col.Table_Name = '<your table name>'

edited Nov 29 '22 at 18:38

Nicholas Franceschina

6,009
6
36
51

answered Sep 18 '08 at 19:29

Guy Starbuck

21,603
7
53
64

1

FYI- this does not necessarily list the columns *in order*. See this answer to similar question if you need the columns in their particular order: http://stackoverflow.com/a/3942921/18511 – Kip Feb 04 '13 at 03:17
10

Actually, I believe you have to also constrain by Schema, right? So, you'd need to add "And COL.TABLE_SCHEMA = ''" as well. – DavidStein May 17 '13 at 15:56
If the above query returns 3 rows, `a`, `b` and `c`, (in that order) then my table has a primary composite key of `abc`? – Kevin Meredith Jul 22 '17 at 19:16

score 43 · Answer 2 · answered Sep 10 '15 at 20:26

It's generally recommended practice now to use the sys.* views over INFORMATION_SCHEMA in SQL Server, so unless you're planning on migrating databases I would use those. Here's how you would do it with the sys.* views:

SELECT 
    c.name AS column_name,
    i.name AS index_name,
    c.is_identity
FROM sys.indexes i
    inner join sys.index_columns ic  ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    inner join sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
    and i.object_ID = OBJECT_ID('<schema>.<tablename>');

To order, add 'ORDER BY ic.key_ordinal ASC' to the query – Ruud van de Beeten Feb 19 '16 at 10:10 — Ruud van de Beeten, Feb 19 '16 at 10:10

score 29 · Answer 3 · answered May 16 '16 at 09:19

This is a solution which uses only sys-tables.

It lists all the primary keys in the database. It returns schema, table name, column name and the correct column sort order for each primary key.

If you want to get the primary key for a specific table, then you need to filter on SchemaName and TableName.

IMHO, this solution is very generic and does not use any string literals, so it will run on any machine.

select 
    s.name as SchemaName,
    t.name as TableName,
    tc.name as ColumnName,
    ic.key_ordinal as KeyOrderNr
from 
    sys.schemas s 
    inner join sys.tables t   on s.schema_id=t.schema_id
    inner join sys.indexes i  on t.object_id=i.object_id
    inner join sys.index_columns ic on i.object_id=ic.object_id 
                                   and i.index_id=ic.index_id
    inner join sys.columns tc on ic.object_id=tc.object_id 
                             and ic.column_id=tc.column_id
where i.is_primary_key=1 
order by t.name, ic.key_ordinal ;

score 11 · Answer 4 · edited Nov 01 '22 at 16:31

11

Is using MS SQL Server you can do the following:

-- List all tables primary keys
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

You can also filter on the table_name column if you want a specific table.

edited Nov 01 '22 at 16:31

JayRizzo

3,234
3
33
49

answered Sep 18 '08 at 19:31

Dwight T

1,457
2
11
20

4

this only lists the key, it does not list the columns in the key – Kip Jan 30 '13 at 21:35
1

This is off to the right start, but needs to be joined with INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as in the answer by Guy Starbuck. – bstrong Jan 05 '16 at 20:26

score 10 · Answer 5 · edited Nov 01 '22 at 16:29

10

I like the INFORMATION_SCHEMA technique, but another I've used is:

exec sp_pkeys 'table'

edited Nov 01 '22 at 16:29

JayRizzo

3,234
3
33
49

answered Oct 07 '08 at 16:06

user12861

2,358
4
23
41

score 9 · Answer 6 · edited Jun 20 '20 at 09:12

Here's another way from the question get table primary key using sql query:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA+'.'+CONSTRAINT_NAME), 'IsPrimaryKey') = 1
  AND TABLE_NAME = '<your table name>'

It uses KEY_COLUMN_USAGE to determine the constraints for a given table
Then uses OBJECTPROPERTY(id, 'IsPrimaryKey') to determine if each is a primary key

score 5 · Answer 7 · edited Dec 01 '17 at 22:26

5

I am telling a simple Technic which I follow

SP_HELP 'table_name'

run this code as query. Mention your table name at place of table_name for which you want to know Primary Key (don't forget the single quotes). The result will show like attached Image. Hope it will help you

edited Dec 01 '17 at 22:26

Shadoninja

1,276
1
12
22

answered Nov 03 '17 at 08:20

Bha15

231
3
8

Make sure you surround your table name in single quotes or the command will not work! – Shadoninja Dec 01 '17 at 18:21

score 4 · Answer 8 · answered Sep 26 '11 at 06:36

--This is another Modified Version which is also an example for Co-Related Query

SELECT TC.TABLE_NAME as [Table_name], TC.CONSTRAINT_NAME as [Primary_Key]
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
 INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
 ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
 WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
 TC.TABLE_NAME IN
 (SELECT [NAME] AS [TABLE_NAME] FROM SYS.OBJECTS 
 WHERE TYPE = 'U')

aked · Answer 9 · 2013-04-21T06:08:03.603

This should list all the constraints ( primary Key and Foreign Keys ) and at the end of query put table name

/* 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 2 · Answer 10 · answered Jun 27 '15 at 00:17

2

This one gives you the columns that are PK.

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'TableName'

answered Jun 27 '15 at 00:17

Tanner Ornelas

602
1
8
17

score 2 · Answer 11 · answered Aug 26 '19 at 01:13

For a comma separated list of primary key columns for a given TableName and Schema:

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)

score 2 · Answer 12 · edited Dec 04 '15 at 13:05

2

The system stored procedure sp_help will give you the information. Execute the following statement:

execute sp_help table_name

edited Dec 04 '15 at 13:05

Sabyasachi Mishra

1,677
2
31
49

answered Sep 18 '08 at 19:23

boes

2,835
2
23
28

score 2 · Answer 13 · edited Mar 08 '14 at 22:49

SELECT t.name AS 'table', i.name AS 'index', it.xtype,

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 1 
        AND k.id = t.id)
    AS 'column1',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 2 
        AND k.id = t.id)
    AS 'column2',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 3
        AND k.id = t.id)
    AS 'column3',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 4
        AND k.id = t.id)
    AS 'column4',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 5
        AND k.id = t.id)
    AS 'column5',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 6
        AND k.id = t.id)
    AS 'column6',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 7
        AND k.id = t.id)
    AS 'column7',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 8 
        AND k.id = t.id)
    AS 'column8',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 9 
        AND k.id = t.id)
    AS 'column9',

(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k 
    ON k.indid = i.indid 
        AND c.colid = k.colid 
        AND c.id = t.id 
        AND k.keyno = 10
        AND k.id = t.id)
    AS 'column10',

FROM sysobjects t
    INNER JOIN sysindexes i ON i.id = t.id 
    INNER JOIN sysobjects it ON it.parent_obj = t.id AND it.name = i.name

WHERE it.xtype = 'PK'
ORDER BY t.name, i.name

For some reason I get an error on the sub queries returning multiple values. I tried commenting out each of the subqueries to see if I could pin point it, but they all seem to fail on the same table, which has only one field in it's index. Any ideas why this would happen? — Marshall, Sep 09 '11 at 13:39
I found that the problem was when a table function was listed. Not sure why, but the field counts for a column (i.e. column1) was 2. My fix was to change the final WHERE clause to "WHERE it.xtype='PK' AND t.[type] = 'U'". — Marshall, Sep 09 '11 at 15:17
I also prettied it up using the isnull function on each column select to avoid seeing 'NULL' in my result set. For example: ,ISNULL( (SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k ON k.indid = i.indid AND c.colid = k.colid AND c.id = t.id AND k.keyno = 1 AND k.id = t.id), '' ) AS 'column1' — Marshall, Sep 09 '11 at 15:17

score 1 · Answer 14 · answered Jan 20 '10 at 02:57

Thanks Guy.

With a slight variation I used it to find all the primary keys for all the tables.

SELECT A.Name,Col.Column_Name from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col ,
    (select NAME from dbo.sysobjects where xtype='u') AS A
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY '
    AND Col.Table_Name = A.Name

score 1 · Answer 15 · edited Apr 05 '17 at 13:45

1

Below query will list primary keys of particular table:

SELECT DISTINCT
    CONSTRAINT_NAME AS [Constraint],
    TABLE_SCHEMA AS [Schema],
    TABLE_NAME AS TableName
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    TABLE_NAME = 'mytablename'

edited Apr 05 '17 at 13:45

freedomn-m

27,664
8
35
57

answered Oct 04 '16 at 11:27

Anjan Kant

4,090
41
39

score 1 · Answer 16 · answered May 07 '18 at 07:47

If you are looking to do your own ORM or generate code from a given table, then this might be what you are looking form:

declare @table varchar(100) = 'mytable';

with cte as
(
    select 
        tc.CONSTRAINT_SCHEMA
        , tc.CONSTRAINT_TYPE
        , tc.TABLE_NAME
        , ccu.COLUMN_NAME
        , IS_NULLABLE
        , DATA_TYPE
        , CHARACTER_MAXIMUM_LENGTH
        , NUMERIC_PRECISION
    from 
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
        inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu on tc.TABLE_NAME=ccu.TABLE_NAME  and tc.TABLE_SCHEMA=ccu.TABLE_SCHEMA
        inner join information_schema.COLUMNS c on ccu.COLUMN_NAME=c.COLUMN_NAME and ccu.TABLE_NAME=c.TABLE_NAME and ccu.TABLE_SCHEMA=c.TABLE_SCHEMA
    where 
        tc.table_name=@table
        and 
        ccu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
    union 
    select TABLE_SCHEMA,'COLUMN', TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@table
    and COLUMN_NAME not in (select COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = @table)
)
select 
    cast(iif(CONSTRAINT_TYPE='PRIMARY KEY',1,0) as bit) PrimaryKey
    ,cast(iif(CONSTRAINT_TYPE='FOREIGN KEY',1,0) as bit) ForeignKey
    ,cast(iif(CONSTRAINT_TYPE='COLUMN',1,0) as bit) NotKey
    ,COLUMN_NAME
    ,cast(iif(is_nullable='NO',0,1) as bit) IsNullable
    , DATA_TYPE
    , CHARACTER_MAXIMUM_LENGTH
    , NUMERIC_PRECISION 
from 
    cte 
order by 
    case CONSTRAINT_TYPE 
        when 'PRIMARY KEY' then 1 
        when 'FOREIGN KEY' then 2 
        else 3 end
    , COLUMN_NAME

Here is what the result would look like:

    <table cellspacing=0 border=1>
     <tr>
      <td style=min-width:50px>PrimaryKey</td>
      <td style=min-width:50px>ForeignKey</td>
      <td style=min-width:50px>NotKey</td>
      <td style=min-width:50px>COLUMN_NAME</td>
      <td style=min-width:50px>IsNullable</td>
      <td style=min-width:50px>DATA_TYPE</td>
      <td style=min-width:50px>CHARACTER_MAXIMUM_LENGTH</td>
      <td style=min-width:50px>NUMERIC_PRECISION</td>
     </tr>
     <tr>
      <td style=min-width:50px>1</td>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>LectureNoteID</td>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>int</td>
      <td style=min-width:50px>NULL</td>
      <td style=min-width:50px>10</td>
     </tr>
     <tr>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>1</td>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>LectureId</td>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>int</td>
      <td style=min-width:50px>NULL</td>
      <td style=min-width:50px>10</td>
     </tr>
     <tr>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>1</td>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>NoteTypeID</td>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>int</td>
      <td style=min-width:50px>NULL</td>
      <td style=min-width:50px>10</td>
     </tr>
     <tr>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>1</td>
      <td style=min-width:50px>Body</td>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>nvarchar</td>
      <td style=min-width:50px>-1</td>
      <td style=min-width:50px>NULL</td>
     </tr>
     <tr>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>1</td>
      <td style=min-width:50px>DisplayOrder</td>
      <td style=min-width:50px>0</td>
      <td style=min-width:50px>int</td>
      <td style=min-width:50px>NULL</td>
      <td style=min-width:50px>10</td>
     </tr>
    </table>

Hamed Nikzad · Answer 17 · 2018-07-07T20:42:41.583

If Primary Key and type needed, this query may be useful:

SELECT L.TABLE_SCHEMA, L.TABLE_NAME, L.COLUMN_NAME, R.TypeName
FROM(
    SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
)L
LEFT JOIN (
    SELECT
    OBJECT_NAME(c.OBJECT_ID) TableName ,c.name AS ColumnName ,t.name AS TypeName
    FROM sys.columns AS c
    JOIN sys.types AS t ON c.user_type_id=t.user_type_id
)R ON L.COLUMN_NAME = R.ColumnName AND L.TABLE_NAME = R.TableName

score 1 · Answer 18 · edited Jan 26 '12 at 09:22

1

SELECT A.TABLE_NAME as [Table_name], A.CONSTRAINT_NAME as [Primary_Key]
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
 WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

edited Jan 26 '12 at 09:22

Cosmin

21,216
5
45
60

answered Sep 26 '11 at 06:18

Manjunath C Bhat

51
1

score 0 · Answer 19 · edited Mar 23 '17 at 19:56

I found this useful, gives a list of tables with a comma separate list of the columns and then also a comma separate list of which ones are the primary key

SELECT T.TABLE_SCHEMA, T.TABLE_NAME, 
STUFF((
    SELECT ', ' + C.COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS C
        WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA
        AND T.TABLE_NAME = C.TABLE_NAME
        FOR XML PATH ('')
    ), 1, 2, '') AS Columns,
STUFF((
SELECT ', ' + C.COLUMN_NAME 
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    ON C.TABLE_SCHEMA = TC.TABLE_SCHEMA
    AND C.TABLE_NAME = TC.TABLE_NAME
    WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA
    AND T.TABLE_NAME = C.TABLE_NAME
    AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
    FOR XML PATH ('')
), 1, 2, '') AS [Key]
FROM INFORMATION_SCHEMA.TABLES T
ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME

Something like this (?) : 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:11

Soenhay · Answer 20 · 2017-03-23T19:51:23.083

This version displays the schema, the table name and an ordered, comma separated list of primary keys. Object_Id() does not work for link servers so we filter by the table name.

Without the REPLACE(Si1.Column_Name, '', '') it would show the xml opening and closing tags for Column_Name on the database I was testing on. I am not sure why the database required a replace for 'Column_Name' so if someone knows then please comment.

DECLARE @TableName VARCHAR(100) = '';
WITH Sysinfo
    AS (SELECT Kcu.Table_Name
            , Kcu.Table_Schema AS Schema_Name
            , Kcu.Column_Name
            , Kcu.Ordinal_Position
        FROM   [LinkServer].Information_Schema.Key_Column_Usage Kcu
             JOIN [LinkServer].Information_Schema.Table_Constraints AS Tc ON Tc.Constraint_Name = Kcu.Constraint_Name
        WHERE  Tc.Constraint_Type = 'Primary Key')
    SELECT           Schema_Name
                    ,Table_Name
                    , STUFF(
                          (
                             SELECT ', '
                                 , REPLACE(Si1.Column_Name, '', '')
                             FROM    Sysinfo Si1
                             WHERE  Si1.Table_Name = Si2.Table_Name
                             ORDER BY Si1.Table_Name
                                   , Si1.Ordinal_Position
                             FOR XML PATH('')
                          ), 1, 2, '') AS Primary_Keys
    FROM Sysinfo Si2
    WHERE Table_Name = CASE
                       WHEN @TableName NOT IN( '', 'All')
                       THEN @TableName
                       ELSE Table_Name
                    END
    GROUP BY Si2.Table_Name, Si2.Schema_Name;

And the same pattern using George's query:

DECLARE @TableName VARCHAR(100) = '';
WITH Sysinfo
    AS (SELECT S.Name AS Schema_Name
            , T.Name AS Table_Name
            , Tc.Name AS Column_Name
            , Ic.Key_Ordinal AS Ordinal_Position
        FROM   [LinkServer].Sys.Schemas S
             JOIN [LinkServer].Sys.Tables T ON S.Schema_Id = T.Schema_Id
             JOIN [LinkServer].Sys.Indexes I ON T.Object_Id = I.Object_Id
             JOIN [LinkServer].Sys.Index_Columns Ic ON I.Object_Id = Ic.Object_Id
                                                       AND I.Index_Id = Ic.Index_Id
             JOIN [LinkServer].Sys.Columns Tc ON Ic.Object_Id = Tc.Object_Id
                                                  AND Ic.Column_Id = Tc.Column_Id
        WHERE  I.Is_Primary_Key = 1)
    SELECT           Schema_Name
                    ,Table_Name
                    , STUFF(
                          (
                             SELECT ', '
                                 , REPLACE(Si1.Column_Name, '', '')
                             FROM    Sysinfo Si1
                             WHERE  Si1.Table_Name = Si2.Table_Name
                             ORDER BY Si1.Table_Name
                                   , Si1.Ordinal_Position
                             FOR XML PATH('')
                          ), 1, 2, '') AS Primary_Keys
    FROM Sysinfo Si2
    WHERE Table_Name = CASE
                       WHEN @TableName NOT IN('', 'All')
                       THEN @TableName
                       ELSE Table_Name
                    END
    GROUP BY Si2.Table_Name, Si2.Schema_Name;

score 0 · Answer 21 · answered Aug 02 '17 at 05:52

Sys.Objects Table contains row for each user-defined, schema-scoped object .

Constraints created like Primary Key or others will be the object and Table name will be the parent_object

Query sys.Objects and collect the Object's Ids of Required Type

declare @TableName nvarchar(50)='TblInvoice' -- your table name
declare @TypeOfKey nvarchar(50)='PK' -- For Primary key

SELECT Name FROM sys.objects
WHERE type = @TypeOfKey 
AND  parent_object_id = OBJECT_ID (@TableName)

score 0 · Answer 22 · edited Feb 01 '18 at 23:09

May I suggest a more accurate simple answer to the original question below

SELECT 
KEYS.table_schema, KEYS.table_name, KEYS.column_name, KEYS.ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE keys
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS 
    ON cons.TABLE_SCHEMA = keys.TABLE_SCHEMA 
    AND cons.TABLE_NAME = keys.TABLE_NAME 
    AND cons.CONSTRAINT_NAME = keys.CONSTRAINT_NAME
WHERE cons.CONSTRAINT_TYPE = 'PRIMARY KEY'

Notes:

Some of the answers above are missing a filter for just primary key columns!
I'm using below in a CTE to join to a larger column listing to provide the metadata from a source to feed BIML generation of staging tables and SSIS code

score 0 · Answer 23 · answered Apr 19 '18 at 05:16

Might be lately posted but hopefully this will help someone to see primary key list in sql server by using this t-sql query:

SELECT  schema_name(t.schema_id) AS [schema_name], t.name AS TableName,        
    COL_NAME(ic.OBJECT_ID,ic.column_id) AS PrimaryKeyColumnName,
    i.name AS PrimaryKeyConstraintName
FROM    sys.tables t 
INNER JOIN sys.indexes AS i  on t.object_id=i.object_id 
INNER JOIN  sys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID
                            AND i.index_id = ic.index_id 
WHERE OBJECT_NAME(ic.OBJECT_ID) = 'YourTableNameHere'

You can see the list of all foreign keys by using this query if you may want:

SELECT
f.name as ForeignKeyConstraintName
,OBJECT_NAME(f.parent_object_id) AS ReferencingTableName
,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ReferencingColumnName
,OBJECT_NAME (f.referenced_object_id) AS ReferencedTableName
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS 
 ReferencedColumnName  ,delete_referential_action_desc AS 
DeleteReferentialActionDesc ,update_referential_action_desc AS 
UpdateReferentialActionDesc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
 --WHERE OBJECT_NAME(f.parent_object_id) = 'YourTableNameHere' 
 --If you want to know referecing table details 
 WHERE OBJECT_NAME(f.referenced_object_id) = 'YourTableNameHere' 
 --If you want to know refereced table details 
ORDER BY f.name

score 0 · Answer 24 · answered Apr 25 '18 at 05:14

I found this from my friend, very effective if you are looking for all the table's primary keys under particular schema.

SELECT tc.constraint_name AS IndexName,tc.table_name AS TableName,tc.table_schema
AS SchemaName,kc.column_name AS COLUMN_NAME
FROM information_schema.table_constraints tc,information_schema.key_column_usage kc
WHERE tc.constraint_type = 'PRIMARY KEY' AND kc.table_name = tc.table_name AND kc.table_schema = tc.table_schema
AND kc.constraint_name = tc.constraint_name AND tc.table_schema='<SCHEMA_NAME>'

score 0 · Answer 25 · answered Jul 12 '21 at 13:10

0

Probably the simplest solution :)

EXEC sp_pkeys YourTable

answered Jul 12 '21 at 13:10

happybits

636
7
10

skenneth · Answer 26 · 2023-02-24T16:23:00.367

Here's my attempt at it for listing keys' data types as well based on any key constraints for primary or foreign keys.

SELECT 
    ksu.table_name as TableName, 
    ksu.column_name as ColumnName, 
    tc.constraint_type as ConstraintType, 
    c.Data_Type as DataType, 
    ksu.ordinal_position as OrdinalPosition
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ksu
    ON tc.Table_Name = ksu.Table_Name 
    and tc.Constraint_Name = ksu.Constraint_Name
JOIN INFORMATION_SCHEMA.COLUMNS c
    ON c.Table_Name = ksu.Table_Name 
    and c.Column_Name = ksu.Column_Name
WHERE tc.Constraint_Type = 'Primary Key' 
    --or tc.Constraint_Type = 'Foreign Key'
GROUP BY 
    ksu.table_name, ksu.column_name, tc.constraint_type, c.Data_Type, ksu.ordinal_position
ORDER BY ksu.table_name, ksu.column_name, tc.constraint_type, c.Data_Type

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Mar 01 '23 at 00:36

score 0 · Answer 27 · edited Jan 06 '16 at 03:36

0

Give this a try:

SELECT
    CONSTRAINT_CATALOG AS DataBaseName,
    CONSTRAINT_SCHEMA AS SchemaName,
    TABLE_NAME AS TableName,
    CONSTRAINT_Name AS PrimaryKey
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_TYPE = 'Primary Key' and Table_Name = 'YourTable'

edited Jan 06 '16 at 03:36

Tanner Ornelas

602
1
8
17

answered Sep 18 '08 at 19:32

Austin Salonen

49,173
15
109
139

score -1 · Answer 28 · edited Oct 13 '21 at 13:04

-1

If you need it in Oracle it is so simple.

SELECT `Constraint_Name`
  FROM `All_Constraints`
 WHERE `Constraint_Type` = `'P'`
   AND `Owner` = `'your schema here';`

edited Oct 13 '21 at 13:04

piet.t

11,718
21
43
52

answered Oct 12 '21 at 15:55

Msfata

19
2

How do you list the primary key of a SQL Server table?

28 Answers28

Linked

Related