Query to find the Primary Key of a table

      No Comments on Query to find the Primary Key of a table

This is the simple query which I use to find the primary key of a table. Not a rocket science. But simple and handy.
Inner query version
[sourcecode language=’sql’]
SELECT TABLE_NAME, COLUMN_NAME PRIMARY_KEY,CONSTRAINT_NAME,OWNER
FROM USER_CONS_COLUMNS UCC WHERE
UCC.CONSTRAINT_NAME=(
SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS UC WHERE UPPER(UC.TABLE_NAME)=UPPER(‘ADDRESS’) AND UC.CONSTRAINT_TYPE=’P’
)
[/sourcecode]

Or Join Version
[sourcecode language=’sql’]
SELECT UCC.TABLE_NAME, UCC.COLUMN_NAME PRIMARY_KEY, UCC.CONSTRAINT_NAME,UCC.OWNER
FROM USER_CONS_COLUMNS UCC,USER_CONSTRAINTS UC
WHERE UCC.CONSTRAINT_NAME= UC.CONSTRAINT_NAME
AND UPPER(UC.TABLE_NAME)=UPPER(‘ADDRESS’)
AND UC.CONSTRAINT_TYPE=’P’

[/sourcecode]

where ‘ADDRESS’ is the name of the table

Leave a Reply

Your email address will not be published. Required fields are marked *