I recently had to rename a table (and a column and FK/PK contraints) in SQL Server 2000 without losing an data. There did not seem to be an obvious DDL T-SQL statements for performing this action, so I used sp_rename to directly fiddle with object names.
我最近不得不在SQL Server 2000中重命名表(以及列和FK / PK约束)而不会丢失数据。似乎没有明显的DDL T-SQL语句来执行此操作,因此我使用sp_rename直接编写对象名称。
Was this the only solution to the problem? (other, than give the table the correct name in the first place - doh!)
这是解决问题的唯一方法吗? (其他,首先给表格正确的名字 - doh!)
4 个解决方案
#1
13
sp_rename is the correct way to do it.
sp_rename是正确的方法。
EXEC sp_rename 'Old_TableName', 'New_TableName'
#2
2
Ya
EXEC sp_rename 'Old_TableName', 'New_TableName' work fine but are any key word like "alter tabel old_name to new_name "
Ya EXEC sp_rename'Old_TableName','New_TableName'工作正常但是任何关键字如“alter tabel old_name to new_name”
#3
0
Maybe not the only: I guess you could always toy with the master database and update the table name there - but this is highly unrecommendable.
也许不是唯一的:我猜你总是可以玩master数据库并在那里更新表名 - 但这是非常不可取的。
#4
0
There is a solution that can let you work concurrently with both old and new versions of the table. This is particularly important if your data is replicated and/or is accessed through client interface (meaning old versions of the client interface will still work with the old table name):
有一个解决方案可以让您同时使用表的新旧版本。如果您的数据被复制和/或通过客户端界面访问(这意味着旧版本的客户端界面仍将使用旧表名称),这一点尤为重要:
- Modify the constraints (including FKs) on your table through "
ALTER TABLE" command -
Do not change table name or field name but create a view such as:
不要更改表名或字段名,而是创建一个视图,例如:
SELECT oldTable.oldField1 as newField1, ...将oldTable.oldField1选为newField1,...
save it as newTable (and, if requested, distribute it on your different servers)
将其保存为newTable(如果需要,将其分发到不同的服务器上)
通过“ALTER TABLE”命令修改表上的约束(包括FK)
Note that you cannot modify your PK this way.
请注意,您无法以这种方式修改PK。
#1
13
sp_rename is the correct way to do it.
sp_rename是正确的方法。
EXEC sp_rename 'Old_TableName', 'New_TableName'
#2
2
Ya
EXEC sp_rename 'Old_TableName', 'New_TableName' work fine but are any key word like "alter tabel old_name to new_name "
Ya EXEC sp_rename'Old_TableName','New_TableName'工作正常但是任何关键字如“alter tabel old_name to new_name”
#3
0
Maybe not the only: I guess you could always toy with the master database and update the table name there - but this is highly unrecommendable.
也许不是唯一的:我猜你总是可以玩master数据库并在那里更新表名 - 但这是非常不可取的。
#4
0
There is a solution that can let you work concurrently with both old and new versions of the table. This is particularly important if your data is replicated and/or is accessed through client interface (meaning old versions of the client interface will still work with the old table name):
有一个解决方案可以让您同时使用表的新旧版本。如果您的数据被复制和/或通过客户端界面访问(这意味着旧版本的客户端界面仍将使用旧表名称),这一点尤为重要:
- Modify the constraints (including FKs) on your table through "
ALTER TABLE" command -
Do not change table name or field name but create a view such as:
不要更改表名或字段名,而是创建一个视图,例如:
SELECT oldTable.oldField1 as newField1, ...将oldTable.oldField1选为newField1,...
save it as newTable (and, if requested, distribute it on your different servers)
将其保存为newTable(如果需要,将其分发到不同的服务器上)
通过“ALTER TABLE”命令修改表上的约束(包括FK)
Note that you cannot modify your PK this way.
请注意,您无法以这种方式修改PK。