Thursday, December 8, 2011

Script to Rename Constraints in PL/SQL using Dynamic SQL


Below script renames constraint names as per table_name,column_name and constraint_type

set serveroutput on
BEGIN
for i in (select dba_constraints.owner,dba_constraints.constraint_name,dba_constraints.table_name, DBA_CONS_COLUMNS.column_name,constraint_type
              from dba_constraints,DBA_CONS_COLUMNS
              where DBA_CONS_COLUMNS.owner = 'USER'
              and dba_constraints.constraint_name = DBA_CONS_COLUMNS.constraint_name
               and constraint_type not in ('P','U')
           
               )
LOOP
execute immediate 'Alter table '||i.owner||'.'||i.table_name||' RENAME CONSTRAINT '||i.constraint_name||' TO '||i.table_name||'_'||i.column_name||'_'||i.constraint_type;
end loop;
end;

No comments:

Post a Comment