Query below lists tables with their primary key columns. For composite keys all columns are listed together with positions in the key.

Query

select
   all_cons_columns.owner as schema_name,
   all_cons_columns.table_name, 
   all_cons_columns.column_name, 
   all_cons_columns.position, 
   all_constraints.status
from all_constraints, all_cons_columns 
where 
   all_constraints.constraint_type = 'P'
   and all_constraints.constraint_name = all_cons_columns.constraint_name
   and all_constraints.owner = all_cons_columns.owner
order by 
   all_cons_columns.owner,
   all_cons_columns.table_name, 
   all_cons_columns.position

Columns

• schema_name - schema name
• table_name - table name
• column_name - primary key column name
• position - primary key column position
• status - primary key status (ENABLED / DISABLED)

Rows

• One row represents one column of primary key in a table
• Scope of rows: all tables in a database
• Ordered by schema, table name, column position

Sample results

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free