15 November 2012

SQL RENAME Statement

With RENAME statement you can rename a table.

Some of the relational database management system (RDBMS) does not support this command, because this is not standardizing statement.
For example renaming a table through MS SQL Server you must use storage procedure SP_RENAME.

Syntax for SQL RENAME is:

RENAME TABLE {tbl_name} TO {new_tbl_name};
Where {tbl_name} table that exists in the current database, and {new_tbl_name} is new table name.

As for Oracle may also be use the following option:

ALTER TABLE {tbl_name} RENAME TO {new_tbl_name};
As Example
CREATE TABLE employees
( id NUMBER(6),
name VARCHAR(20)
);
INSERT INTO employees( id, name ) values( 1, 'name 1');
INSERT INTO employees( id, name ) values( 2, 'name 2');
INSERT INTO employees( id, name ) values( 3, 'name 3');
SELECT * FROM employees;
SELECT Output:
idname
--------------------------------------
1name 1
2name 2
3name 3
RENAME TABLE employees TO employees_new;

SELECT * FROM employees_new;
SELECT Output:
idname
--------------------------------------
1name 1
2name 2
3name 3

SQL RENAME NOTES

1)The minimum version that supports table renaming is Oracle 8i. All the dependencies of the table will automatically updated. No need of updating them after wards