22,208
社区成员
发帖
与我相关
我的任务
分享
sp_msforeachtable 'ALTER TABLE ? ADD newcol int DEFAULT 9 WITH values',@wherehead=' and name like ''log_%_%'''
2000可用
declare hcforeach cursor global
for
select ' alter table '+quotename(Name)+' add newcol int exec(''update '+quotename(Name)+' set newcol=9 '')'
from sysobjects
where objectproperty(id,'isusertable')=1
and name like 'log[_][0-9]%[_][0-9]%'--自定義條件
exec sp_msforeach_worker '?'
EXEC sp_msforeachtable @command1 = N'
if ''?'' like ''log[_][0-9]%[_]%''
begin
ALTER TABLE ? ADD newcol int
exec(''UPDATE ? SET newcol = 9'')
end'
set nocount on
declare @dt datetime set @dt='2008-01-01'
declare @tbname varchar(50),@sql varchar(8000)
while @dt<=cast('2008-12-31' as datetime)
begin
set @sql='alter table log_'+rtrim(month(@dt))+'_'+rtrim(day(@dt))+' add newcol int'
exec(@sql)
set @sql='update log_'+rtrim(month(@dt))+'_'+rtrim(day(@dt))+' set newcol=9'
exec(@sql)
set @dt=dateadd(day,1,@dt)
print('log_'+rtrim(month(@dt))+'_'+rtrim(day(@dt))+' 处理完毕')
end
sp_msforeachtable 'ALTER TABLE ? ADD newcol int DEFAULT 9 WITH values'