重命名列SQL Server 2008

本文翻译自：Rename column SQL Server 2008

I am using SQL Server 2008 and Navicat. 我正在使用SQL Server 2008和Navicat。 I need to rename a column in a table using SQL. 我需要使用SQL重命名表中的列。

ALTER TABLE table_name RENAME COLUMN old_name to new_name;

This statement doesn't work. 此声明不起作用。

---

#1楼

参考：https://stackoom.com/question/16NUk/重命名列SQL-Server

---

#2楼

Use sp_rename 使用sp_rename

EXEC sp_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

See: SQL SERVER – How to Rename a Column Name or Table Name 请参阅： SQL SERVER - 如何重命名列名或表名

Documentation: sp_rename (Transact-SQL) 文档： 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. 请记住使用单引号括起您的值。

---

#3楼

尝试：

EXEC sp_rename 'TableName.OldName', 'NewName', 'COLUMN'

---

#4楼

It would be a good suggestion to use an already built-in function but another way around is to: 使用已经内置的功能是一个很好的建议，但另一种方法是：

1. Create a new column with same data type and NEW NAME. 创建具有相同数据类型和新名称的新列。
2. Run an UPDATE/INSERT statement to copy all the data into new column. 运行UPDATE / INSERT语句将所有数据复制到新列中。
3. Drop the old column. 放下旧栏目。

The benefit behind using the sp_rename is that it takes care of all the relations associated with it. 使用sp_rename的好处是它可以处理与之关联的所有关系。

From the documentation : 从文档 ：

sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. 每当重命名PRIMARY KEY或UNIQUE约束时，sp_rename都会自动重命名关联的索引。 If a renamed index is tied to a PRIMARY KEY constraint, the PRIMARY KEY constraint is also automatically renamed by sp_rename. 如果重命名的索引绑定到PRIMARY KEY约束，则sp_rename也会自动重命名PRIMARY KEY约束。 sp_rename can be used to rename primary and secondary XML indexes. sp_rename可用于重命名主XML索引和辅助XML索引。

---

#5楼

Alternatively to SQL , you can do this in Microsoft SQL Server Management Studio. 除SQL ，您可以在Microsoft SQL Server Management Studio中执行此操作。 Here are a few quick ways using the GUI: 以下是使用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. 当您需要一次重命名多个列时，这种方式更可取。

1. Right-click on the table that contains the column that needs renaming. 右键单击包含需要重命名的列的表。
2. Click Design . 单击设计 。
3. 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 :) 注意：我知道OP专门要求SQL解决方案，认为这可能有助于其他人:)}

---

#6楼

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. 消息15248，级别11，状态1，过程sp_rename，行238参数@objname不明确或声明的@objtype（COLUMN）错误。

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';