Stored Procedure to rename (replace) PK name?

Hi:

I need to rename all PKs in a schema. I would like to write a procedure to do this globally. Before I start this, I would like to know if I am trying to re-invent the wheel here. If someone has the procedure to do this, I would certainly appreciate it if you can share with us.

Basically, I would like to have something like this:

• assuming PK is made of only one column in atable Replace_PK (tableName IN Varchar2, columnName IN Varchar2, newPKName IN Varchar2)

Find existing PK name in the table
IF not found

      run dynamic sql"alter table tableName add constraint Primary key(columnName)"

else

      find all Fks that reference this old PK ,
      save all Fk's info in a cursor
      using dynamic sql to
         drop all FKs
         drop the old PK
         create new PK with new name
         re-establish all Fks

end if;

Is there any better or easiler way to do this?

TIA. Guang

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 04 2000 - 21:05:29 CDT