How do I rename a column in a database table using SQL?

Solution 1:

Specifically for SQL Server, use sp_rename

USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO

Solution 2:

On PostgreSQL (and many other RDBMS), you can do it with regular ALTER TABLE statement:

=> SELECT * FROM Test1;
 id | foo | bar 
----+-----+-----
  2 |   1 |   2

=> ALTER TABLE Test1 RENAME COLUMN foo TO baz;
ALTER TABLE

=> SELECT * FROM Test1;
 id | baz | bar 
----+-----+-----
  2 |   1 |   2

Solution 3:

In MySQL, the syntax is ALTER TABLE ... CHANGE:

ALTER TABLE <table_name> CHANGE <column_name> <new_column_name> <data_type> ...

Note that you can't just rename and leave the type and constraints as is; you must retype the data type and constraints after the new name of the column.