删除一列数据，后一列的数据自动往前，最后一列自动补为null

/*

作者：baikaishui

时间：2005-4-23

删除一列数据，后一列的数据自动往前，最后一列自动补为null
*/
--建立测试数据
create table abc (id int,x1 int,x2 int,x3 int,x4 int,x5 int,x6 int,x7 int,x8 int)
insert abc
select 1,1,2,3,4,5,6,7,8
go

create procedure del_col
@column nvarchar(100),
@tablename nvarchar(100)
as


declare @colid int
declare @colmax int
declare @colum nvarchar(100)
select @colid=syscolumns.colid from syscolumns join sysobjects on syscolumns.id=sysobjects.id
where sysobjects.type='u'and sysobjects.status>0 and sysobjects.name=@tablename and syscolumns.name=@column
--print @colid
select @colmax=max(syscolumns.colid) from syscolumns join sysobjects on syscolumns.id=sysobjects.id
where sysobjects.type='u'and sysobjects.status>0 and sysobjects.name=@tablename
--print @colmax
declare @i int
declare @colum1 nvarchar(100)
declare @colum2 nvarchar(100)
declare @sql nvarchar(500)
set @i=@colid
while (@i<=@colmax)
begin
set @sql=''
if (@i<@colmax)
begin
select @colum1=syscolumns.name from syscolumns join sysobjects on syscolumns.id=sysobjects.id
where sysobjects.type='u'and sysobjects.status>0 and sysobjects.name=@tablename and syscolumns.colid=@i
select @colum2=syscolumns.name from syscolumns join sysobjects on syscolumns.id=sysobjects.id
where sysobjects.type='u'and sysobjects.status>0 and sysobjects.name=@tablename and syscolumns.colid=@i+1
print @colum1
print @colum2
set @sql=N'update '+@tablename+' set '+@colum1+'='+@colum2
print @sql
exec sp_executesql @sql
end
else
begin
select @colum1=syscolumns.name from syscolumns join sysobjects on syscolumns.id=sysobjects.id
where sysobjects.type='u'and sysobjects.status>0 and sysobjects.name=@tablename and syscolumns.colid=@colmax
print @colum1
set @sql=N'update '+@tablename+' set '+@colum1+'=null '
print @sql
exec sp_executesql @sql
end

set @i=@i+1
end

go

exec del_col 'x3','abc'
select * from abc

--删除测试数据
DROP PROCEDURE del_col

drop table abc