34,838
社区成员




declare @sql varchar(max)
declare @name varchar(100)
set @sql=''
declare mycursor cursor for
select name from sysobjects where type = 'U' and name like 'TBDATA_%'
open mycursor
fetch next from mycursor
into @name
while (@@fetch_status=0)
begin
set @sql=@sql+' alter table '+@name+' add TBDATA_ZX1 VARCHAR(10)'+' alter table '+@name+' add TBDATA_ZX2 VARCHAR(10)'+' alter table '+@name+' add TBDATA_ZX3 VARCHAR(10)'
fetch next from mycursor
into @name
end
close mycursor
deallocate mycursor
exec(@sql)
select * From sysobjects where xtype = 'U' and name like 'TBDATA_%'
--拼动态语句,如果你的表明有规律的话!
declare @sql varchar(8000)
declare @i int
set @i = 1
while(@i <= 12) --TBDATE_0012 从0001到0012
begin
select @sql = isnull(@sql,'') + ' alter table TBDATA_00' + ltrim(@i) + ' add ...'
set @i = @i + 1
end
exec(@sql)
alter table TBDATA_0001
add TBDATA_ZX1 VARCHAR(10) NULL
declare @sql varchar(max)
set @sql=''
select @sql=@sql+' alter table '+ name+' add TBDATA_ZX1 VARCHAR(10)'+' alter table '+name+' add TBDATA_ZX2 VARCHAR(10)'+' alter table '+name+' add TBDATA_ZX3 VARCHAR(10)'
from sysobjects where type = 'U' and name like 'TBDATA%'
exec(@sql)