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

J

12

138

If I wish to simply rename a column (not change its type or constraints, just its name) in an SQL database using SQL, how do I do that? Or is it not possible?

This is for any database claiming to support SQL, I'm simply looking for an SQL-specific query that will work regardless of actual database implementation.

Jinajingle answered 6/10, 2008 at 14:44 Comment(2)

Seconding skaffman, this is not a "SQL" question, it is (maybe) a "SQLServer" question. – Hols 6/10, 2008 at 15:4

Any database system that purports to use SQL. Oracle, MySQL, etc...I'm looking for a database-independent answer. – Jinajingle 6/10, 2008 at 15:8

B

113

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

Banquet answered 6/10, 2008 at 14:57 Comment(4)

This is not supported in MySQL, is it? – Craftsman 21/10, 2011 at 11:41

No,its not supported in MySQL – Bullis 13/5, 2013 at 19:16

This is also not supported in Microsoft SQL Server. Instead use sp_rename as per Galwegian's answer: https://mcmap.net/q/55203/-how-do-i-rename-a-column-in-a-database-table-using-sql – Sievers 11/12, 2013 at 13:15

I believe MySQL 8.0 supports this syntax now – Aruabea 23/4, 2020 at 17:41

B

149

Specifically for SQL Server, use sp_rename

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

Brianbriana answered 6/10, 2008 at 14:45 Comment(5)

It seems Microsoft-specific and nothing in the original query indicated a Microsoft DBMS. – Banquet 6/10, 2008 at 14:54

Yes, the answer I was looking for is "standard" SQL, and not dependent on any particular implementation. However, it is a good answer for anyone using Microsoft's system. – Jinajingle 6/10, 2008 at 15:12

Note to all: don't use something like EXEC sp_rename '[Sales].[SalesTerritory].[TerritoryID]', '[TerrID]', 'COLUMN';, as it will literally rename the column to [TerrID], which will break a lot of stuff... talking from experience here... – Bonaparte 14/10, 2021 at 8:54

[Yes], [I'm] [not] [sure] [why] [the] [proverbial] [Bill] [Gates] [chose] [to] [use] [square] [brackets] [around] [identifier] [names]. [It's] [really] [dumb]! – Claus 7/6, 2022 at 11:54

@ReversedEngineer aesthetic – Rummy 19/3 at 21:51

B

113

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

Banquet answered 6/10, 2008 at 14:57 Comment(4)

This is not supported in MySQL, is it? – Craftsman 21/10, 2011 at 11:41

No,its not supported in MySQL – Bullis 13/5, 2013 at 19:16

This is also not supported in Microsoft SQL Server. Instead use sp_rename as per Galwegian's answer: https://mcmap.net/q/55203/-how-do-i-rename-a-column-in-a-database-table-using-sql – Sievers 11/12, 2013 at 13:15

I believe MySQL 8.0 supports this syntax now – Aruabea 23/4, 2020 at 17:41

H

41

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.

Helterskelter answered 4/6, 2015 at 22:45 Comment(2)

it is tested in mySQL database – Helterskelter 5/6, 2015 at 11:33

We can just rename column without affecting type/constraint with RENAME syntax. Rename a column in MySQL – Selfrespect 15/9, 2018 at 15:42

V

31

Unfortunately, for a database independent solution, you will need to know everything about the column. If it is used in other tables as a foreign key, they will need to be modified as well.

ALTER TABLE MyTable ADD MyNewColumn OLD_COLUMN_TYPE;
UPDATE MyTable SET MyNewColumn = MyOldColumn;
-- add all necessary triggers and constraints to the new column...
-- update all foreign key usages to point to the new column...
ALTER TABLE MyTable DROP COLUMN MyOldColumn;

For the very simplest of cases (no constraints, triggers, indexes or keys), it will take the above 3 lines. For anything more complicated it can get very messy as you fill in the missing parts.

However, as mentioned above, there are simpler database specific methods if you know which database you need to modify ahead of time.

Villatoro answered 25/10, 2013 at 1:56 Comment(2)

Directly answers the question, even if it's not exactly what the asker was looking for... – Roselani 17/7, 2015 at 18:52

In the comment, OP actually clarified that this is exactly what he was looking for. – Arthralgia 15/8, 2018 at 5:5

N

25

I think this is the easiest way to change column name.

SP_RENAME 'TABLE_NAME.OLD_COLUMN_NAME','NEW_COLUMN_NAME'

Nickelson answered 31/7, 2015 at 7:9 Comment(2)

this worked for me on SQL Server. I am not sure if this works on other DBMS – Limicoline 11/1, 2017 at 14:1

Worked on SqlServer , Thanks – Disunite 23/3, 2023 at 7:26

S

12

In sql server you can use

exec sp_rename '<TableName.OldColumnName>','<NewColumnName>','COLUMN'

or

sp_rename '<TableName.OldColumnName>','<NewColumnName>','COLUMN'

Suppletion answered 13/10, 2016 at 11:4 Comment(0)

E

9

In Informix, you can use:

RENAME COLUMN TableName.OldName TO NewName;

This was implemented before the SQL standard addressed the issue - if it is addressed in the SQL standard. My copy of the SQL 9075:2003 standard does not show it as being standard (amongst other things, RENAME is not one of the keywords). I don't know whether it is actually in SQL 9075:2008.

Erythropoiesis answered 10/10, 2008 at 22:5 Comment(2)

No RENAME in SQL 2008 Draft too. – Bellybutton 22/12, 2009 at 4:36

Not AS - use TO. RENAME COLUMN TableName.OldName TO NewName; www-01.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/… – Nguyetni 16/12, 2014 at 12:41

A

6

You can use the following command to rename the column of any table in SQL Server:

exec sp_rename 'TableName.OldColumnName', 'New colunmName'

Adrenalin answered 6/4, 2017 at 2:55 Comment(0)

F

3

ALTER TABLE is standard SQL. But it's not completely implemented in many database systems.

Film answered 6/10, 2008 at 15:2 Comment(2)

I accepted bortz' answer over yours because he gave a detailed explanation. Nevertheless, I upvoted you. – Jinajingle 6/10, 2008 at 15:10

@Jinajingle - I only added my answer to acknowledge that "ALTER TABLE" isn't just PostgreSQL, it's pretty common. – Film 6/10, 2008 at 18:4

I

2

The standard would be ALTER TABLE, but that's not necessarily supported by every DBMS you're likely to encounter, so if you're looking for an all-encompassing syntax, you may be out of luck.

Integrity answered 10/10, 2008 at 22:10 Comment(0)

M

1

Alternatively to SQL, you can do this in Microsoft SQL Server Management Studio, from the table Design Panel.

First Way

Slow double-click on the column. The column name will become an editable text box.

Second Way

SqlManagement Studio>>DataBases>>tables>>specificTable>>Column Folder>>Right Click on column>>Reman

Third Way

Table>>RightClick>>Design

Mylonite answered 13/11, 2014 at 10:27 Comment(0)

W

0

To rename you have to change the column

e.g

Suppose

*registration is Table Name

newRefereeName is a column name That I want to change to refereeName SO my SQL Query will be*

ALTER TABLE 'registration' CHANGE 'newRefereeName' 'refereeName' VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;

Wrand answered 10/3, 2022 at 9:17 Comment(0)

Recommended topics

Hot tags