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?
如果我只想用SQL重命名一个列(不更改它的类型或约束，只更改它的名称)，我该如何做呢?还是不可能?
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.
这适用于任何声称支持SQL的数据库，我只是在寻找一个特定于SQL的查询，无论实际的数据库实现如何，该查询都可以工作。
11 个解决方案
#1
81
On PostgreSQL (and many other RDBMS), you can do it with regular ALTER TABLE statement:
在PostgreSQL(和许多其他RDBMS)上，可以使用常规的ALTER TABLE语句:
essais=> SELECT * FROM Test1;
id | foo | bar
----+-----+-----
2 | 1 | 2
essais=> ALTER TABLE Test1 RENAME COLUMN foo TO baz;
ALTER TABLE
essais=> SELECT * FROM Test1;
id | baz | bar
----+-----+-----
2 | 1 | 2
#2
99
Specifically for SQL Server, use sp_rename
特别是对于SQL Server，使用sp_rename
USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO
#3
28
In MySQL, the syntax is ALTER TABLE ... CHANGE:
在MySQL中，语法是ALTER TABLE…变化:
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.
注意，不能只重命名并保留类型和约束;必须在列的新名称之后重新输入数据类型和约束。
#4
16
I think this is the easiest way to change column name.
我认为这是更改列名最简单的方法。
SP_RENAME 'TABLE_NAME.OLD_COLUMN_NAME','NEW_COLUMN_NAME'
#5
15
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.
对于最简单的情况(没有约束、触发器、索引或键)，它将使用上述3行。对于任何更复杂的东西，当你填满缺失的部分时，它会变得非常混乱。
However, as mentioned above, there are simpler database specific methods if you know which database you need to modify ahead of time.
但是，如上所述，如果您知道需要提前修改哪些数据库，则会有更简单的数据库特定方法。
#6
9
In Informix, you can use:
在Informix中，您可以使用:
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.
这是在SQL标准解决问题之前实现的——如果它是在SQL标准中处理的。我复制的SQL 9075:2003标准并没有显示它是标准的(除了其他之外，RENAME不是关键字之一)。我不知道它是否在SQL 9075:2008中。
#7
4
In sql server you can use
在sql server中可以使用
exec sp_rename '<TableName.OldColumnName>','<NewColumnName>','COLUMN'
or
或
sp_rename '<TableName.OldColumnName>','<NewColumnName>','COLUMN'
#8
3
ALTER TABLE is standard SQL. But it's not completely implemented in many database systems.
ALTER TABLE是标准的SQL。但是在许多数据库系统中还没有完全实现。
#9
2
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.
标准是可修改的，但这并不是所有你可能遇到的DBMS都支持的，所以如果你正在寻找一个包罗万象的语法，你可能会倒霉。
#10
2
You can use the following command to rename the column of any table in SQL Server:
您可以使用以下命令重命名SQL Server中的任何表的列:
exec sp_rename 'TableName.OldColumnName', 'New colunmName'
#11
-1
Alternatively to SQL, you can do this in Microsoft SQL Server Management Studio, from the table Design Panel.
对于SQL，您可以在Microsoft SQL Server Management Studio中，从表设计面板中执行此操作。
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
>数据库>>表>>特异性表>> >列文件夹>>右击>0 >1列Reman
Third Way
第三条道路
Table>>RightClick>>Design
表> > RightClick > >的设计
#1
81
On PostgreSQL (and many other RDBMS), you can do it with regular ALTER TABLE statement:
在PostgreSQL(和许多其他RDBMS)上，可以使用常规的ALTER TABLE语句:
essais=> SELECT * FROM Test1;
id | foo | bar
----+-----+-----
2 | 1 | 2
essais=> ALTER TABLE Test1 RENAME COLUMN foo TO baz;
ALTER TABLE
essais=> SELECT * FROM Test1;
id | baz | bar
----+-----+-----
2 | 1 | 2
#2
99
Specifically for SQL Server, use sp_rename
特别是对于SQL Server，使用sp_rename
USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO
#3
28
In MySQL, the syntax is ALTER TABLE ... CHANGE:
在MySQL中，语法是ALTER TABLE…变化:
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.
注意，不能只重命名并保留类型和约束;必须在列的新名称之后重新输入数据类型和约束。
#4
16
I think this is the easiest way to change column name.
我认为这是更改列名最简单的方法。
SP_RENAME 'TABLE_NAME.OLD_COLUMN_NAME','NEW_COLUMN_NAME'
#5
15
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.
对于最简单的情况(没有约束、触发器、索引或键)，它将使用上述3行。对于任何更复杂的东西，当你填满缺失的部分时，它会变得非常混乱。
However, as mentioned above, there are simpler database specific methods if you know which database you need to modify ahead of time.
但是，如上所述，如果您知道需要提前修改哪些数据库，则会有更简单的数据库特定方法。
#6
9
In Informix, you can use:
在Informix中，您可以使用:
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.
这是在SQL标准解决问题之前实现的——如果它是在SQL标准中处理的。我复制的SQL 9075:2003标准并没有显示它是标准的(除了其他之外，RENAME不是关键字之一)。我不知道它是否在SQL 9075:2008中。
#7
4
In sql server you can use
在sql server中可以使用
exec sp_rename '<TableName.OldColumnName>','<NewColumnName>','COLUMN'
or
或
sp_rename '<TableName.OldColumnName>','<NewColumnName>','COLUMN'
#8
3
ALTER TABLE is standard SQL. But it's not completely implemented in many database systems.
ALTER TABLE是标准的SQL。但是在许多数据库系统中还没有完全实现。
#9
2
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.
标准是可修改的，但这并不是所有你可能遇到的DBMS都支持的，所以如果你正在寻找一个包罗万象的语法，你可能会倒霉。
#10
2
You can use the following command to rename the column of any table in SQL Server:
您可以使用以下命令重命名SQL Server中的任何表的列:
exec sp_rename 'TableName.OldColumnName', 'New colunmName'
#11
-1
Alternatively to SQL, you can do this in Microsoft SQL Server Management Studio, from the table Design Panel.
对于SQL，您可以在Microsoft SQL Server Management Studio中，从表设计面板中执行此操作。
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
>数据库>>表>>特异性表>> >列文件夹>>右击>0 >1列Reman
Third Way
第三条道路
Table>>RightClick>>Design
表> > RightClick > >的设计