How to get primary key column in Oracle

Question

I need to get the name of the primary key column    In the input  I only have the table name

User · Accepted Answer

SELECT cols table name  cols column name  cols position  cons status  cons owner FROM all constraints cons  all cons columns cols WHERE cols table name    TABLE NAME  AND cons constraint type    P  AND cons constraint name   cols constraint name AND cons owner   cols owner ORDER BY cols table name  cols position    Make sure that  TABLE NAME  is in upper case since Oracle stores table names in upper case

User · Answer

Save the following script as something like findPK sql   set verify off accept TABLE NAME char prompt  Table name gt    SELECT cols column name FROM all constraints cons NATURAL JOIN all cons columns cols WHERE cons constraint type    P  AND table name   UPPER   amp TABLE NAME      It can then be called using   findPK

User · Answer

Try This Code Here I created a table for get primary key column in oracle which is called test and then query  create table test   id int  name varchar2 20   city varchar2 20   phone int  constraint pk id name city primary key  id name city      SELECT cols table name  cols column name  cols position  cons status  cons owner FROM all constraints cons  all cons columns cols WHERE cols table name    TEST  AND cons constraint type    P  AND cons constraint name   cols constraint name AND cons owner   cols owner  ORDER BY cols table name  cols position

User · Answer

Same as the answer from  Richie  but a bit more concise    Query for user constraints only  SELECT column name FROM all cons columns WHERE constraint name       SELECT constraint name FROM user constraints    WHERE UPPER table name    UPPER  tableName   AND CONSTRAINT TYPE    P      Query for all constraints  SELECT column name FROM all cons columns WHERE constraint name       SELECT constraint name FROM all constraints    WHERE UPPER table name    UPPER  tableName   AND CONSTRAINT TYPE    P

User · Answer

Select constraint name constraint type from user constraints where table name          TABLE NAME         This will list the primary key and then   Select column name position from user cons cloumns where constraint name    PK XYZ         This will give you the column  here PK XYZ is the primay key name

[oracle] How to get primary key column in Oracle?

Examples related to oracle