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

Question

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

User · Answer

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

User · Answer

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

User · Answer

Specifically for SQL Server  use sp rename  USE AdventureWorks  GO EXEC sp rename  Sales SalesTerritory TerritoryID    TerrID    COLUMN   GO

User · Answer

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

User · Answer

In sql server you can use   exec sp rename   lt TableName OldColumnName gt     lt NewColumnName gt    COLUMN    or  sp rename   lt TableName OldColumnName gt     lt NewColumnName gt    COLUMN

User · Answer

Specifically for SQL Server  use sp rename  USE AdventureWorks  GO EXEC sp rename  Sales SalesTerritory TerritoryID    TerrID    COLUMN   GO

User · Answer

In sql server you can use   exec sp rename   lt TableName OldColumnName gt     lt NewColumnName gt    COLUMN    or  sp rename   lt TableName OldColumnName gt     lt NewColumnName gt    COLUMN

User · Answer

You can use the following command to rename the column of any table in SQL Server   exec sp rename  TableName OldColumnName    New colunmName

User · Answer

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

User · Answer

In MySQL  the syntax is ALTER TABLE     CHANGE   ALTER TABLE  lt table name gt  CHANGE  lt column name gt   lt new column name gt   lt data type gt        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

User · Answer

On PostgreSQL  and many other RDBMS   you can do it with regular ALTER TABLE statement     gt  SELECT   FROM Test1   id   foo   bar  ---- ----- -----   2     1     2    gt  ALTER TABLE Test1 RENAME COLUMN foo TO baz  ALTER TABLE    gt  SELECT   FROM Test1   id   baz   bar  ---- ----- -----   2     1     2

User · Answer

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

User · Answer

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

User · Answer

On PostgreSQL  and many other RDBMS   you can do it with regular ALTER TABLE statement     gt  SELECT   FROM Test1   id   foo   bar  ---- ----- -----   2     1     2    gt  ALTER TABLE Test1 RENAME COLUMN foo TO baz  ALTER TABLE    gt  SELECT   FROM Test1   id   baz   bar  ---- ----- -----   2     1     2

User · Answer

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

User · Answer

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

User · Answer

In MySQL  the syntax is ALTER TABLE     CHANGE   ALTER TABLE  lt table name gt  CHANGE  lt column name gt   lt new column name gt   lt data type gt        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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I think this is the easiest way to change column name   SP RENAME  TABLE NAME OLD COLUMN NAME   NEW COLUMN NAME

User · Answer

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

User · Answer

Specifically for SQL Server  use sp rename  USE AdventureWorks  GO EXEC sp rename  Sales SalesTerritory TerritoryID    TerrID    COLUMN   GO

User · Answer

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

User · Answer

You can use the following command to rename the column of any table in SQL Server   exec sp rename  TableName OldColumnName    New colunmName

User · Answer

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

User · Answer

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

User · Answer

On PostgreSQL  and many other RDBMS   you can do it with regular ALTER TABLE statement     gt  SELECT   FROM Test1   id   foo   bar  ---- ----- -----   2     1     2    gt  ALTER TABLE Test1 RENAME COLUMN foo TO baz  ALTER TABLE    gt  SELECT   FROM Test1   id   baz   bar  ---- ----- -----   2     1     2

User · Answer

I think this is the easiest way to change column name   SP RENAME  TABLE NAME OLD COLUMN NAME   NEW COLUMN NAME

User · Answer

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

User · Answer

Specifically for SQL Server  use sp rename  USE AdventureWorks  GO EXEC sp rename  Sales SalesTerritory TerritoryID    TerrID    COLUMN   GO

User · Answer

On PostgreSQL  and many other RDBMS   you can do it with regular ALTER TABLE statement     gt  SELECT   FROM Test1   id   foo   bar  ---- ----- -----   2     1     2    gt  ALTER TABLE Test1 RENAME COLUMN foo TO baz  ALTER TABLE    gt  SELECT   FROM Test1   id   baz   bar  ---- ----- -----   2     1     2

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

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to rename