在SQL Server中动态修改数据表结构

if exists(select * from sysobjects where lower(name)=lower('up_AddCol4Obbr') and xtype='P')
 drop procedure up_AddCol4Obbr 
go

create procedure up_AddCol4Obbr
 @strTable nvarchar(100),
 @strColName nvarchar(100),
 @strType nvarchar(100)
as
begin
 declare @strSQL nvarchar(1000)

 if not exists(select * from syscolumns where lower(name)=lower(@strColName) and id in (select id from sysobjects where lower(name)=lower(@strTable) ))
 begin  
  select @strSQL = N'alter table ' + @strTable + ' add ' + @strColName + ' ' + @strType

  exec sp_executesql @strSQL
 end
 else
 begin
  select @strSQL = N'alter table ' + @strTable + ' alter column ' + @strColName + ' ' + @strType

  exec sp_executesql @strSQL
 end
end
go

if exists(select * from sysobjects where lower(name)=lower('up_CheckCols4Obbr') and xtype='P')
 drop procedure up_CheckCols4Obbr 
go

create procedure up_CheckCols4Obbr
as
begin
 declare @nColCnts smallint, @nShopCnts smallint, @nCol smallint, @nShop smallint
 declare @strColName nvarchar(30), @strType nvarchar(50), @strTable nvarchar(50)

 select @nColCnts = count(*) from u_obbc
 select @nShopCnts = count(*) from u_obbs
 select @nCol=1, @nShop=1, @strTable='U_OBBR'

 while @nCol< =@nColCnts
 begin
  select @strColName = 'TC' + right('00'+cast(@nCol as nvarchar(10)),2)
  select @strType = dType from joyouext.dbo.u_obbc where colId = @nCol

  exec up_AddCol4Obbr @strTable, @strColName, @strType

  select @nCol = @nCol + 1
 end

 while @nShop< =@nShopCnts
 begin
  set @nCol=1
  while @nCol< =@nColCnts
  begin
   select @strColName = 'BC' + right('00'+cast(@nShop as nvarchar(10)),2) + right('00'+cast(@nCol as nvarchar(10)),2)
   select @strType = dType from joyouext.dbo.u_obbc where colId = @nCol
 
   exec up_AddCol4Obbr @strTable, @strColName, @strType
 
   select @nCol = @nCol + 1
  end

  select @nShop = @nShop + 1
 end 
end
go

本文转自foresun  51CTO博客，原文链接：http://blog.51cto.com/foresun/44182，如需转载请自行联系原作者