SQL Reference ยท Data Definition
SQL RENAME tables and columns
Reference for renaming in SQL: ALTER TABLE RENAME TO, RENAME COLUMN, and the MySQL, SQL Server and Oracle variants, with effects on views and keys.
Renaming changes the name of a table or column without touching its data. The standard way is ALTER TABLE ... RENAME, and most databases support it, but the exact spelling varies enough that it is worth a closer look.
Do not confuse renaming an object with an alias. AS renames a column or table only inside one query; RENAME permanently changes the schema, so code that uses the old name breaks.
ALTER TABLE categories RENAME TO product_groups; ALTER TABLE products RENAME COLUMN stock TO units_in_stock;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
ALTER TABLE table_name RENAME TO new_table_name; ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
| Part | Description |
|---|---|
RENAME TO |
Gives the table a new name. |
RENAME COLUMN ... TO |
Renames one column. |
Dialect differences
| Dialect | Rename table | Rename column |
|---|---|---|
| SQLite | ALTER TABLE a RENAME TO b |
ALTER TABLE t RENAME COLUMN a TO b (3.25+) |
| PostgreSQL | ALTER TABLE a RENAME TO b |
ALTER TABLE t RENAME COLUMN a TO b |
| MySQL | RENAME TABLE a TO b or ALTER TABLE a RENAME TO b |
ALTER TABLE t RENAME COLUMN a TO b (8.0) or CHANGE a b type |
| SQL Server | EXEC sp_rename 'a', 'b' |
EXEC sp_rename 't.a', 'b', 'COLUMN' |
| Oracle | ALTER TABLE a RENAME TO b or RENAME a TO b |
ALTER TABLE t RENAME COLUMN a TO b |
-- MySQL RENAME TABLE orders TO orders_old, orders_new TO orders; -- atomic swap -- SQL Server EXEC sp_rename 'products.stock', 'units_in_stock', 'COLUMN';
Usage notes
- Dependencies: SQLite and PostgreSQL rewrite references in indexes, foreign keys and views automatically. Application code, stored procedures and reports are not updated: search for the old name.
- SQL Server's
sp_renamedoes not fix dependent views or procedures and warns you about it. - Zero-downtime deployments often add the new column, copy data, switch code and only then drop the old one, instead of renaming in place.
- Atomic table swap: MySQL's multi-table
RENAME TABLEswaps names in one step; PostgreSQL can do the same in a transaction. - Case and quoting: a name created with quotes or mixed case in PostgreSQL must be quoted every time.
- Rename the table first and its indexes after, if you want matching names.
Related
ALTER TABLE,AS(aliases),CREATE VIEW,DROP TABLE- Lessons: ALTER and DROP, Aliases