重命名列 SQL Server 2008

Rename column SQL Server 2008

提问by Serhio g. Lazin

I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL.

我正在使用 SQL Server 2008 和 Navicat。我需要使用 SQL 重命名表中的列。

ALTER TABLE table_name RENAME COLUMN old_name to new_name;

This statement doesn't work.

这个说法行不通。

回答by Habib

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.

请记住使用单引号将您的值括起来。

回答by Carrie Kendall

Alternatively to SQL, you can do this in Microsoft SQL Server Management Studio. Here are a few quick ways using the GUI:

或者SQL，您可以在 Microsoft SQL Server Management Studio 中执行此操作。以下是使用 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.

1. 右键单击包含需要重命名的列的表。
2. 单击设计。
3. 在表设计面板中，单击并编辑要更改的列名称的文本框。

For example:

例如：

_{NOTE:I know OP specifically asked for SQL solution, thought this might help others :)}

_{注意：我知道 OP 专门要求 SQL 解决方案，认为这可能对其他人有帮助:)}

回答by TechDo

Try:

尝试：

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

回答by Taher

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

回答by NeverHopeless

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.
3. Drop the old column.

1. 创建一个具有相同数据类型和 NEW NAME 的新列。
2. 运行 UPDATE/INSERT 语句将所有数据复制到新列中。
3. 删除旧列。

The benefit behind using the sp_renameis 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. 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.

每当重命名 PRIMARY KEY 或 UNIQUE 约束时，sp_rename 都会自动重命名关联的索引。如果重命名的索引绑定到 PRIMARY KEY 约束，则 sp_rename 也会自动重命名 PRIMARY KEY 约束。sp_rename 可用于重命名主要和次要 XML 索引。

回答by Alexandre Neukirchen

You can use sp_renameto rename a column.

您可以使用sp_rename重命名列。

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 COLUMNinforms the server that the rename is for the column, and can also be used to rename tables, indexand alias data type.

第一个参数是要修改的对象，第二个参数是将赋予该对象的新名称，第三个参数COLUMN通知服务器重命名是针对 的column，也可以用于重命名tables、index和alias data type。

回答by HonzaB

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 OldColumnNamemust not be in []. It will not work.
• Don't put NewColumnNameinto [], it will result into [[NewColumnName]].

• 将OldColumnName不能在[]。不起作用。
• 不要把NewColumnName进入[]，它会导致成[[NewColumnName]]。

回答by Tahir77667

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

Sql Server management studio 有一些系统定义的存储过程（SP），
其中一个用于重命名列。SP 是sp_rename

Syntax:sp_rename '[table_name].old_column_name', 'new_column_name'
For further help refer this article: sp_rename by Microsoft Docs

语法：sp_rename '[table_name].old_column_name', 'new_column_name'
如需进一步帮助，请参阅这篇文章：Microsoft Docs 的 sp_rename

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.

注意：在执行此 SP 时，sql server 将给您一条警告消息，如“注意：更改对象名称的任何部分都可能破坏脚本和存储过程”。仅当您编写自己的涉及列的 sp 时，这才是关键在您即将更改的表中。

回答by Rikin Patel

Improved version of @Taher

@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

回答by Dave Cole

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

或者您可以在 SQL Management Studio 中的列上缓慢单击两次并通过 UI 重命名它...