There are occasions where you can change existing table name to a new name. In the olden days somebody has given incorrect table name, where the name confuses to many developers.
Data is already present in the table. So, you want to change its name. It is possible without affecting existing data with RENAME clause.
I am giving rules and sample SQL query for both ORACLE and DB2 databases.
ORACLE database
Using RENAME you can rename a table to new name.
Checkout DROP and TRUNCATE here.
Sample SQL:
RENAME myvendors to vendor;
Note: You need to take care or re-built about all the constraints that used the old Table name.
Points to remember
- You cannot rollback the RENAME
- Indexes, Integrity constraints and permissions automatically apply to a new table
- All the other objects(Views, Store procedures and Functions) will be invalid, since they use old table name
- You can rename table, view, sequence, or private synonym
DB2 database
The syntax for RENAME in DB2 also same. But, There are some critical rules to follow for renaming a table.
RENAME TABLEEMPTOEMPLOYEE
This is also possible to rename a index
RENAME TABLEEMPTOEMPLOYEE
Rules to remember
- All the catalog tables updated with new table name
- All the indexes transferred to new table
- The objects that dependent on old table will be invalidated.
Srinimf 