Skip to content
Tutorials Center
My learning

Topics

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.

SQL
ALTER TABLE categories RENAME TO product_groups;
ALTER TABLE products RENAME COLUMN stock TO units_in_stock;

Try it

SQL Rename a column, then a table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Renames update dependent objects; the old name fails (fails on purpose) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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
SQL
-- 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_rename does 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 TABLE swaps 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.