Stored Procedure to rename (replace) PK name?

From: Guang Mei <zlmei_at_hotmail.com>
Date: Thu, 05 Oct 2000 13:27:26 GMT
Message-Id: <10640.118577@fatcity.com>

Hi:

I need to rename some 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 could share with us.

Basically, I would like to have something like this:

assuming PK is made of only one column in a table
stored procedure: Replace_PK (tableName IN Varchar2, columnName IN Varchar2, newPKName IN Varchar2)
code goes like this: 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

Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

Share information about yourself, create your own public profile at Received on Thu Oct 05 2000 - 08:27:26 CDT