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

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.

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

SP_RENAME 'TABLE_NAME.OLD_COLUMN_NAME','NEW_COLUMN_NAME'

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

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.

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.

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

SP_RENAME 'TABLE_NAME.OLD_COLUMN_NAME','NEW_COLUMN_NAME'

Specifically for SQL Server, use sp_rename

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

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.

Specifically for SQL Server, use sp_rename

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

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

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.

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.

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

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

Specifically for SQL Server, use sp_rename

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

In sql server you can use

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

or

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

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

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.

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.

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

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.

Specifically for SQL Server, use sp_rename

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

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.

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

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.

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.

In sql server you can use

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

or

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

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

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

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

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.