RENAME in SQL how it works in two popular databases

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

This is called RENAME clause in ORACLE. Using this clause you can rename a table to new name. You can read for DROP and TRUNCATE clauses here. These are all part of alter Table scenarios.

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
RENAME in two popular databases
Stocksnap.io

DB2 database

The syntax for RENAME in DB2 also same. But, There are some critical rules to follow for renaming a table.

RENAME TABLE EMP TO EMPLOYEE

This is also possible to rename a index

RENAME TABLE EMP TO EMPLOYEE

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.
Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Start Discussion

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s