Re: Changing Table Ownership
Herb Blacker wrote:
>
> I have a *very* large database (~360 tables) which will be converted from
> 5.x to 7.x. In the interim, I would like to change the ownership of all
> tables in the database to a single user. DOes anyone know of a simpler
> way to do this besides doing a dbexport, vi'ing the file to substitute
> the new owner globally, and then dbimporting the whole darned thing?
> Thanks in advance,
> Herb
> -------------------------------------------------------------------------
> Herb Blacker
> Cimarron, Inc.
> Database Administrator - DOL Job Corps San Marcos, Texas
> ------------------------------------------------------------------------
Hi Herb,
You can update the ownership of all tables by SQL:
UPDATE SYSTABLES
SET OWNER = "new_owner"
WHERE TABID >= 100 -- all userdefined tables have a id >=100
It updates tables and other objects like synonyms, views.
If you want only tables to have a new owner, please use the condition
UPDATE .....
WHERE TABID >= 100
AND TABTYPE = "T"
I think it works in both versions, 5.x and 7.x.
Best Regards
Ruediger Papke