Friday, April 20, 2007

Find all tables missing a primary key in an Oracle database

Typically all tables in a database have primary keys. For some reason in ours some primary keys have disappeared. It must've been a botched export re-import operation. Anyway, here is an Oracle data dictionary query returning all tables in the current schema without a primary key.

select table_name 
from user_tables
where table_name not in
(
select distinct table_name
from user_constraints
where constraint_type='P'
)
order by table_name;