Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Stored Procedure to rename (replace) PK name?
Hi
i think, the beste way to do it is, you have to look for a table in the data
dictionary.
for example.
for all tables you find all_tab_columns
i have done something like that before.
ciao
@kram JARO
<gmei_at_my-deja.com> schrieb in im Newsbeitrag: 8rgnn3$31i$1_at_nnrp1.deja.com...
> 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 Thu Oct 05 2000 - 04:08:13 CDT