[ORACLE] find pk without index

---------------------------------------------------------------------------
-- title : find pk without index
-- author : proud
-- discription : find pk without (unique) index - drop and readd
-- usage : @find_pk_without_idx
-- created : 2010.02.02
-- update date:
---------------------------------------------------------------------------

set serveroutput on

drop table result_pk_without_idx;

create table result_pk_without_idx
(
 script_text varchar2(4000)
)
nologging
compress
;

declare
v_drop_sql varchar2(4000);
v_add_sql varchar2(4000);
v_own varchar2(4000);
v_tab varchar2(4000);
v_con varchar2(4000);
v_idx varchar2(4000);
v_col varchar2(4000);
v_cnt pls_integer;

begin

dbms_output.enable(9999999);

for i in (
         select a.owner
               ,a.table_name
               ,a.constraint_name
               ,a.index_name
           from dba_constraints a
               ,dba_indexes b
          where a.index_name = b.index_name(+)
            and a.constraint_type = 'P'
            and b.index_name is null
         ) loop
v_own := i.owner;
v_tab := i.table_name;
v_con := i.constraint_name;
v_idx := i.index_name;

v_drop_sql := 'alter table '||v_own||'.'||v_tab||' drop constraint '||v_con;
v_add_sql := 'alter table '||v_own||'.'||v_tab||' add constraint '||v_con||' primary key (';

v_cnt := 0;

begin
for c in (
         select column_name
           from dba_cons_columns
          where owner = ''||v_own||''
            and table_name = ''||v_tab||''
          order by position
         ) loop

v_col := c.column_name;

if v_cnt > 0 then
v_add_sql := v_add_sql||','||v_col;
else
v_add_sql := v_add_sql||v_col;
end if;

v_cnt := v_cnt + 1;

end loop;
end;

v_add_sql := v_add_sql||')';

execute immediate 'insert into result_pk_without_idx values ('''||v_drop_sql||''')';
execute immediate 'insert into result_pk_without_idx values ('''||v_add_sql||''')';
execute immediate 'commit';
end loop;

dbms_output.put_line('select * from result_pk_without_idx;');
end;
/