前段时间发现自己之前建的表名字段拼写存在问题,趁该表数据量还不算大的时候,编写脚本修改列名。
1. 比较简单粗暴的方式是:
EXEC sp_rename @objname='[schema].[tablename].[errorcolumnname]',@newname='newname',@objtype='column'
2. 保险方法:【步骤:新增列,将问题列数据copy,删除问题列的约束,删除问题列】
IF COL_LENGTH('tablename', 'errorcolumnname') IS NOT NULL
BEGIN
SET XACT_ABORT ON
begin transaction UpdateColumnName
ALTER TABLE [schema].[tablename] ADD [newname] INT NOT NULL DEFAULT(0)
Exec sp_executesql N'UPDATE [schema].[tablename] SET [newname] = [errorcolumnname]'
DECLARE @constraintName NVARCHAR(100)
SELECT @constraintName = b.name FROM sysobjects b JOIN syscolumns a ON b.id = a.cdefault WHERE a.id = object_id('tablename') AND a.name = 'errorcolumnname'
IF @constraintName IS NOT NULL
BEGIN
EXEC ('ALTER TABLE [schema].[tablename] DROP CONSTRAINT '+ @constraintName)
END
ALTER TABLE [schema].[tablename]
DROP COLUMN errorcolumnname
commit transaction UpdateColumnName
END