Tuesday, 15 September 2009

Retrieving Primary Key Columns

This SQL query will allow you to retrieve details of the primary key columns of a specified table. This information is pulled from the Oracle data dictionary views relating to table columns and constraints:

SELECT  *
FROM    all_tab_columns atc
,       all_cons_columns acc
,       all_constraints ac
WHERE   1 = 1 
AND     atc.column_name = acc.column_name
AND     atc.table_name = ac.table_name
AND     atc.owner = ac.owner
AND     acc.constraint_name = ac.constraint_name
AND     acc.owner = ac.owner
AND     ac.constraint_type = 'P'
AND     ac.table_name = :TABLE_NAME
AND     ac.owner = :TABLE_OWNER;