SQLServer在多个表中都增加一个字段的方法
1、使用游标
declare @sql varchar(200), @name varchar(40)
declare my_cursor scroll cursor for
select name from sysobjects where type = 'u' and name like 'jobs_%'
open my_cursor
fetch next from my_cursor into @name
while(@@fetch_status=0)
begin
print 'processing ' + @name
set @sql = N'alter table testbfcmisuser.' + @name + N' add note varchar(200)'
exec(@sql)
print 'finished'
fetch next from my_cursor into @name
end
close my_cursor
deallocate my_cursor
declare my_cursor scroll cursor for
select name from sysobjects where type = 'u' and name like 'jobs_%'
open my_cursor
fetch next from my_cursor into @name
while(@@fetch_status=0)
begin
print 'processing ' + @name
set @sql = N'alter table testbfcmisuser.' + @name + N' add note varchar(200)'
exec(@sql)
print 'finished'
fetch next from my_cursor into @name
end
close my_cursor
deallocate my_cursor
2、使用临时表
declare @sql varchar(200), @name varchar(100), @count int
select identity(int, 1, 1) as ID, name
into #JobTable
from sysobjects
where type = 'u' and name like 'Jobs_%'
set @count=@@rowcount
while @count>0
begin
select top 1 @name = name
from #JobTable
where id = @count
print 'processing ' + @name
set @sql = N'alter table testbfcmisuser.' + @name + N' add note varchar(200)'
exec(@sql)
print 'finished'
set @count = @count - 1
end
drop table #JobTable