当前位置：文江博客话题详情

重命名 MS SQL Server 2005 中的列

发布于 2024-07-30 14:39:38 字数 67 浏览 5 评论 0原文

使用 SQL（MS SQL Server 2005 变体）重命名表列时的最佳实践是什么？这假设列中有必须保留的数据。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半仙 2024-08-06 14:39:38

您必须使用存储过程来重命名列。以下操作会将您的列从“oldColumnName”重命名为“newColumnName”，而不影响任何数据。

EXEC sp_rename 'tableName.[oldColumnName]', 'newColumnName', 'COLUMN'

显然，您必须手动更新使用旧名称的任何代码/存储过程/SQL。

You have to use a stored proc to rename a column. The following will rename your column from 'oldColumnName' to 'newColumnName' without affecting any data.

EXEC sp_rename 'tableName.[oldColumnName]', 'newColumnName', 'COLUMN'

Obviously you'll have to update any code / stored procs / SQL that uses the old name manually.

回复收藏 0 原文

时常饿 2024-08-06 14:39:38

我今天遇到了同样的问题，解决方案是杀死数据库上的所有进程，因为进程锁定了事务。我执行了过程sp_rename，但问题没有解决。所以我杀死了数据库中的进程并且进程正常工作。

USE MASTER
GO

--Kill all the connections opened in database.
DECLARE @dbname sysname
SET @dbname = 'database_name'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END


SELECT request_session_id
FROM   sys.dm_tran_locks
WHERE  resource_database_id = DB_ID('database_name')

I had the same problem today, and the solution was kill all processes on the database, cause the processes was locked the transactions. I was executed the procedure sp_rename, but the problem was not resolved. So i was kill the processes in the database and the proc works.

USE MASTER
GO

--Kill all the connections opened in database.
DECLARE @dbname sysname
SET @dbname = 'database_name'

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END


SELECT request_session_id
FROM   sys.dm_tran_locks
WHERE  resource_database_id = DB_ID('database_name')

回复收藏 0 原文

~没有更多了~