RENAME TABLE / COLUMNDDL
Buy Me A Coffee

Changing the name of an existing table or column without recreating the object.

Renames a table atomically without moving data. Views and foreign key references to the table may need updating. Most databases support this; the syntax varies (RENAME TABLE in MySQL, ALTER TABLE ... RENAME TO in PostgreSQL).

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.0 RENAME TABLE (table) since 5.0. RENAME COLUMN (column) added in 8.0. On older versions use CHANGE col old_col new_col col_definition.
PostgreSQL ✓ Supported 7.4 Both RENAME TABLE and RENAME COLUMN are via ALTER TABLE. Renaming updates pg_class/pg_attribute metadata instantly with no table rewrite.
SQL Server ✓ Supported 2000 Uses sp_rename system procedure rather than ALTER TABLE syntax. Views, procedures, and other objects that reference the old name are not automatically updated.
Oracle ✓ Supported 9i RENAME (table) is standalone DDL; column rename via ALTER TABLE RENAME COLUMN added in Oracle 9i. Dependent objects (views, procedures) are invalidated and must be recompiled.
SQLite ✓ Supported 3.25.0 ALTER TABLE RENAME TO (table rename) has been available since very early SQLite. RENAME COLUMN was added in 3.25.0 (2018). Views and triggers referencing the old column name are updated automatically.

Details

Table and column renaming is universally supported but the syntax diverges meaningfully. SQL Server's sp_rename is the odd one out — it's a procedure call, not DDL syntax, and critically it does not update references in views, stored procedures, or computed columns. Always run sp_depends or search sys.sql_modules after renaming in SQL Server. Oracle invalidates dependent objects on rename but will revalidate automatically the next time they are accessed if the new name resolves. MySQL's CHANGE syntax for pre-8.0 column renames requires repeating the full column definition, making it error-prone. PostgreSQL and SQLite have the cleanest behavior: standard ALTER TABLE syntax, no object rewrite, and (SQLite 3.25+) automatic reference updates in views and triggers.

Standard Syntax

-- Table ALTER TABLE old_name RENAME TO new_name; -- Column ALTER TABLE t RENAME COLUMN old_col TO new_col;

Version Support

MySQL: Since 5.0 PostgreSQL: Since 7.4 SQL Server: Since 2000 Oracle: Since 9i SQLite: Since 3.25.0

Per-Database Syntax & Notes

MySQL

RENAME TABLE (table) since 5.0. RENAME COLUMN (column) added in 8.0. On older versions use CHANGE col old_col new_col col_definition.

RENAME TABLE old_name TO new_name; -- Column (8.0+): ALTER TABLE t RENAME COLUMN old_col TO new_col; -- Column (pre-8.0): ALTER TABLE t CHANGE old_col new_col INT NOT NULL;

PostgreSQL

Both RENAME TABLE and RENAME COLUMN are via ALTER TABLE. Renaming updates pg_class/pg_attribute metadata instantly with no table rewrite.

ALTER TABLE old_name RENAME TO new_name; ALTER TABLE t RENAME COLUMN old_col TO new_col;

SQL Server

Uses sp_rename system procedure rather than ALTER TABLE syntax. Views, procedures, and other objects that reference the old name are not automatically updated.

-- Table: EXEC sp_rename 'old_name', 'new_name'; -- Column: EXEC sp_rename 'dbo.t.old_col', 'new_col', 'COLUMN';

Oracle

RENAME (table) is standalone DDL; column rename via ALTER TABLE RENAME COLUMN added in Oracle 9i. Dependent objects (views, procedures) are invalidated and must be recompiled.

RENAME old_name TO new_name; ALTER TABLE t RENAME COLUMN old_col TO new_col;

SQLite

ALTER TABLE RENAME TO (table rename) has been available since very early SQLite. RENAME COLUMN was added in 3.25.0 (2018). Views and triggers referencing the old column name are updated automatically.

ALTER TABLE old_name RENAME TO new_name; ALTER TABLE t RENAME COLUMN old_col TO new_col;