Retrieve Primary Key information

Note:  Proofread any scripts before using. Always try scripts on a test instance first. This Blog is not responsible for any damage.

Retrieve primary key information with the following SQL statement:

SELECT CONS.OWNER, COLS.TABLE_NAME, COLS.COLUMN_NAME, COLS.POSITION, CONS.STATUS
FROM ALL_CONSTRAINTS CONS, ALL_CONS_COLUMNS COLS
WHERE COLS.TABLE_NAME LIKE UPPER('%&TABLENAME%')
  AND CONS.CONSTRAINT_TYPE = 'P'
  AND CONS.CONSTRAINT_NAME = COLS.CONSTRAINT_NAME
  AND CONS.OWNER = COLS.OWNER
ORDER BY COLS.TABLE_NAME, COLS.POSITION;

Let's quickly explain the output from this query.

1. OWNER indicates the schema that owns the table.
2. TABLE_NAME is the name of the table (stored in uppercase).
3. COLUMN_NAME is the name of the column that is a part of the primary key. (also stored in uppercase)
4. POSITION is the position in the primary key. A primary key can contain more than one column, so understanding the order of the columns in the primary key is

Very important.
5. STATUS indicates whether the primary key is currently enabled or disabled.