Oracle Script To Find Tables With No Primary Key

Primary keys are so important in databases.  I keep trying to write an effective post about why they are so important.  But I don’t feel I’m doing the subject justice.  So let me start with a related, but shorter post.

At a number of shops, I’ve come across so many tables that did not have a primary key.  Some of these were very important tables in the system!   Bad, bad, bad.

After discovering so many tables that no primary key, at so many shops, I finally wrote an Oracle script to list all the tables that did not have a PK.

I named it:  ltnopk.sql.  Meaning, list tables, with no primary key.

/*
Script to list all the owner.tables with no PK.
Ignores SYS and SYSTEM schemas.
By Rodger Lepinsky
*/

Select     tables.owner || ‘.’ ||
tables.table_name
as   table_owner
From               all_tables      tables,
(Select
owner,
TABLE_NAME,
constraint_type,
CONSTRAINT_NAME
FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE = ‘P’     /* list of all tables with PK */
)  constr
Where              tables.owner = constr.owner (+)
And                tables.table_name = constr.table_name (+)
and   tables.owner <> ‘SYS’
and tables.owner <> ‘SYSTEM’
And                constr.owner IS NULL
And                constr.table_name IS NULL
ORDER BY 1

Note:  this will also find temporary tables, which often will not have a primary key.  If you want to filter the temp tables out, add this line to the WHERE clause:

And tables.DURATION is not null

Investigate away!

Find that culprit!

Advertisements