Rename column SQL Server 2008

Question

I am using SQL Server 2008 and Navicat  I need to rename a column in a table using SQL   ALTER TABLE table name RENAME COLUMN old name to new name    This statement doesn t work

User · Answer

You can use sp rename to rename a column   USE YourDatabase    GO   EXEC sp rename  TableName OldColumnName    NewColumnName    COLUMN     GO     The first parameter is the object to be modified  the second parameter is the new name that will be given to the object  and the third parameter COLUMN informs the server that the rename is for the column  and can also be used to rename tables  index and alias data type

User · Answer

Since I often come here and then wondering how to use the brackets  this answer might be useful for those like me   EXEC sp rename   DB   dbo   Tablename  OldColumnName    NewColumnName    COLUMN       The OldColumnName must not be in     It will not work  Don t put NewColumnName into     it will result into   NewColumnName

User · Answer

Or you could just slow-click twice on the column in SQL Management Studio and rename it through the UI

User · Answer

You should also specify the schema of the table or you might get this error      Msg 15248  Level 11  State 1  Procedure sp rename  Line 238 Either the   parameter  objname is ambiguous or the claimed  objtype  COLUMN  is   wrong    If it is a deployment script I would also recommend adding some additional security to it   IF EXISTS           SELECT 1         FROM sys columns         WHERE             name    OldColumnName  AND             object name object id     TableName        AND     NOT EXISTS           SELECT 1         FROM sys columns         WHERE             name    NewColumnName  AND             object name object id     TableName            EXEC sp RENAME  SchemaName TableName OldColumnName    NewColumnName    COLUMN

User · Answer

In my case  I was using MySQL WorkBench and ALTER TABLE table name RENAME COLUMN old name new name varchar 50  not null      without TO and specify data type of that column   was enough to change the column name

User · Answer

It would be a good suggestion to use an already built-in function but another way around is to    Create a new column with same data type and NEW NAME   Run an UPDATE INSERT statement to copy all the data into new column   Drop the old column    The benefit behind using the sp rename is that it takes care of all the relations associated with it   From the documentation      sp rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed  If a renamed index is tied to a PRIMARY KEY constraint  the PRIMARY KEY constraint is also automatically renamed by sp rename  sp rename can be used to rename primary and secondary XML indexes

User · Answer

Alternatively to SQL  you can do this in Microsoft SQL Server Management Studio  Here are a few quick ways using the GUI  First Way Slow double-click on the column  The column name will become an editable text box   Second Way Right click on column and choose Rename from the context menu  For example    Third Way This way is preferable for when you need to rename multiple columns in one go   Right-click on the table that contains the column that needs renaming  Click Design  In the table design panel  click and edit the textbox of the column name you want to alter   For example   NOTE  I know OP specifically asked for SQL solution  thought this might help others

User · Answer

Run Query       SP RENAME   TableName   ColumnName    NewNameForColumn

User · Answer

Sql Server management studio has some system defined Stored Procedures SP  One of which is used to rename a column The SP is sp rename    Syntax  sp rename   table name  old column name    new column name  For further help refer this article  sp rename by Microsoft Docs  Note  On execution of this SP the sql server will give you a caution message as  Caution  Changing any part of an object name could break scripts and stored procedures  This is critical only if you have written your own sp which involves the column in the table you are about to change

User · Answer

Try   EXEC sp rename  TableName OldName    NewName    COLUMN

User · Answer

Use sp rename  EXEC sp RENAME  TableName OldColumnName     NewColumnName    COLUMN    See  SQL SERVER     How to Rename a Column Name or Table Name  Documentation  sp rename  Transact-SQL   For your case it would be   EXEC sp RENAME  table name old name    new name    COLUMN    Remember to use single quotes to enclose your values

User · Answer

Improved version of  Taher  DECLARE  SchemaName AS VARCHAR 128  DECLARE  TableName AS VARCHAR 128  DECLARE  OldColumnName AS VARCHAR 128  DECLARE  NewColumnName AS VARCHAR 128  DECLARE  ParamValue AS VARCHAR 1000   SET  SchemaName    dbo  SET  TableName    tableName  SET  OldColumnName    OldColumnName  SET  NewColumnName    NewColumnName  SET  ParamValue    SchemaName          TableName          OldColumnName  IF EXISTS       SELECT 1 FROM sys columns WHERE name    OldColumnName AND OBJECT NAME object id     TableName   AND NOT EXISTS       SELECT 1 FROM sys columns WHERE name    NewColumnName AND OBJECT NAME object id     TableName   BEGIN     EXEC sp rename  ParamValue   NewColumnName   COLUMN   END

[sql] Rename column SQL Server 2008

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to alter-table