动态改变数据库表结构的sql存储过程

增加数据库表字段的sql语句
下面这个语句是从一个表的某列取记录，把所有记录增加为要改变表的字段
第一个参数是要改变的表名称
第二个参数是要从哪个表选择列
第三个参数是要选择的列名
第四个参数是要增加字段的数据类型。如：char（12）
alter proc alters @table char(20),@tablename char(20),@columns char(20),@type char(20)
as
begin tran
 declare @sql varchar(150)
 create table m(a char(30),idint int)
 declare @id int
  select @id=id from sysobjects where name=@table
 insert into m(idint) values(@id)
 insert into m(a) select name from syscolumns where id=(select idint from m)
 delete from m where idint=@id
 

 select @sql='select distinct '+@columns +' as col from '+@tablename
 select @sql='select * into t from ( '+@sql+' ) as a'
 exec (@sql)
 select @sql='delete from t where t.col in (select a from m)'
 exec (@sql)
 
 select @sql='alter table t add bh int identity(1,1)'
 exec (@sql)

 create table #t(a int,b char(20))
 select @sql='insert into #t(a) select count(*) from t'
 exec (@sql)
 declare @i int
 select @i=1
 declare @shu int
 select @shu= a from #t
 while (@i<=@shu)
 begin

 select @sql='update #t set b= t.col  from t where t.bh='+convert(char,@i)
 exec (@sql)
 declare @names char(20)
 select @names = b from #t

 select @sql='alter table '+@table +' add '+@names+' '+@type
 exec (@sql)
 select @i=@i+1
 

 end
 select @sql='drop table t'
 exec (@sql)
 select @sql='drop table m'
 exec (@sql)
 drop table #t
commit