Finding Tables With No Primary Key or Unique Index in the Schema

 This script is used for Finding Tables With No Primary Key or Unique Index in the Schema.

SET Heading off

SELECT '------ Tables With No Primary Key or Unique Index in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT distinct(table_name)

  FROM all_tables

 WHERE owner = :b0

MINUS

(SELECT obj1.name

  FROM SYS.user$ user1,

       SYS.user$ user2,

       SYS.cdef$ cdef,

       SYS.con$ con1,

       SYS.con$ con2,

       SYS.obj$ obj1,

       SYS.obj$ obj2

 WHERE user1.name = :b0

   AND cdef.type# = 2

   AND con2.owner# = user2.user#(+)

   AND cdef.robj# = obj2.obj#(+)

   AND cdef.rcon# = con2.con#(+)

   AND obj1.owner# = user1.user#

   AND cdef.con# = con1.con#

   AND cdef.obj# = obj1.obj#

UNION

SELECT idx.table_name

  FROM all_indexes idx

 WHERE idx.owner = :b0

   AND idx.uniqueness = 'UNIQUE')

/