Query below lists:

(A) all tables and their primary keys constraint (PK) accessible to the current user in Oracle database

(B) all tables and their primary keys constraint (PK) in Oracle database

Query was executed under the Oracle 12c Database version.

Query

A. Tables and their primary keys constraint accessible to the current user

select tab.owner as schema_name,
       tab.table_name,
       acc.constraint_name,
       LISTAGG(acc.column_name,',')
              WITHIN GROUP (order by acc.position) as columns,
       con.status
from sys.all_tables tab
left join sys.all_constraints con
          on con.owner = tab.owner
          and con.table_name = tab.table_name
          and con.constraint_type = 'P'
left join sys.all_cons_columns acc 
          on con.owner = acc.owner
          and con.constraint_name = acc.constraint_name
where tab.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000','APEX_040200', 'DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WKSYS', 'PUBLIC',
      'OUTLN', 'APEX_PUBLIC_USER')
group by tab.owner,
         tab.table_name,
         acc.constraint_name,
         con.status
order by tab.owner,
         tab.table_name;

B. If you have privilege on dba_tab_columns and dba_tables

select tab.owner as schema_name,
       tab.table_name,
       acc.constraint_name,
       LISTAGG(acc.column_name,',')
              WITHIN GROUP (order by acc.position) as columns,
       con.status
from sys.dba_tables tab
left join sys.dba_constraints con
          on con.owner = tab.owner
          and con.table_name = tab.table_name
          and con.constraint_type = 'P'
left join sys.dba_cons_columns acc
          on con.owner = acc.owner
          and con.constraint_name = acc.constraint_name
where tab.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000','APEX_040200', 'DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WKSYS', 'PUBLIC',
      'OUTLN', 'APEX_PUBLIC_USER')
group by tab.owner,
         tab.table_name,
         acc.constraint_name,
         con.status
order by tab.owner,
         tab.table_name;

Columns

• schema_name - table schema name
• table_name - table name
• constraint_name - table's PK constraint name
• columns - list of table's PK columns separated with ','
• status - table's PK status (ENABLED / DISABLED)

Rows

• One row represents one table in a database
• Scope of rows: all tables and their PK constraint if exist in a database
• Ordered by schema, table_name

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