特别注意:根据以下过程修改过的表,其数据可能会发生不可预测的变化。本人经实践后所得。有些原本有数据的列修改后变成Null,有些新增列的数据是乱码。新建的表,没有数据或许可以参照一下操作。请慎用。
---------------------------------
--开启系统表写功能以便创建存储过程
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
GO
-------开启系统表写功能完成
-------创建存储过程
CREATE PROC sp_addColumn
@TableName sysname, --要添加字段的表名
@FieldDefine nvarchar(1000), --要添加的列的定义,定义必须符合ALTER TABLE tbname ADD规范
@Position int=-1 --添加列位置,其值为1~表的总列数,比如1,表示添加为第1列,
-- 如果@Position无效,则仅添加列
AS
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@TableName),'IsUserTable'),0)=0
BEGIN
RAISERROR(N'无效的表名 "%s"',12,16,@TableName)
return
END
--添加字段
DECLARE @s nvarchar(4000)
SET @s=N'ALTER TABLE '+QUOTENAME(@TableName)+N' ADD '+@FieldDefine
BEGIN TRAN
EXEC sp_executesql @s
IF @@ERROR=0
BEGIN
DECLARE @maxcolid int
SELECT @Position=ISNULL(@Position,0),@maxcolid=MAX(colid)
FROM syscolumns
WHERE id=OBJECT_ID(@TableName)
IF @Position>0 AND @Position<@maxcolid
UPDATE syscolumns SET
colid=CASE colid WHEN @maxcolid THEN @Position ELSE colid+1 END,
colorder=CASE colid WHEN @maxcolid THEN @Position ELSE colid+1 END
WHERE id=OBJECT_ID(@TableName)
AND colid>=@Position
COMMIT TRAN
END
ELSE
ROLLBACK TRAN
GO
-----------------存储过程创建完成
--执行新增列到特定位置语句
exec sp_addColumn 'ZDelCancelInvoice','InvoiceNo char(50)',3
----------执行完成
--------关闭系统表修改功能
EXEC sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
go
--------关闭系统表修改功能完成
--删除存储过程
drop proc sp_addColumn
-----删除存储过程完成
------------------------------------------------------------