金蝶云星空数据细节——临时表手动删除(分批清理+进度报告)原创
12人赞赏了该文章
1,487次浏览
编辑于2022年06月23日 11:28:02
小改了一下金蝶云星空临时表的清理脚本,分50一批进行清理,并及时打印进度,让等待不再那么漫长。
脚本如下:
--查询截止到前一小时的临时表数据量 select count(1) from sys.tables t where name like 'tmp%' and not exists(select 1 from T_BAS_TEMPORARYTABLENAME where FTABLENAME=t.name and FCREATEDATE>DATEADD(HOUR,-1, GETDATE())) --删除临时表 if object_id('temptb','table')>0 drop table temptb; declare @sql varchar(max) declare @icount int declare @I int set @sql='drop table ' set @i=1 select name,IDENTITY(int,1,1) id into temptb from sys.tables t where name like 'tmp%' and not exists(select 1 from T_BAS_TEMPORARYTABLENAME where FTABLENAME=t.name and FCREATEDATE>DATEADD(HOUR,-1, GETDATE())) select @icount=@@ROWCOUNT while @i<@icount begin select @sql=@sql+name+',' from temptb where id between @i and @i+49 if @@ROWCOUNT>0 set @sql=substring(@sql,1,len(@sql)-1)+';' set @i=@i+50 print 'process:'+convert(varchar(100),@i)+' of '+convert(varchar(100),@icount) exec(@sql) set @sql='drop table ' end if object_id('temptb','table')>0 drop table temptb;
效果如下:
周末愉快!!!!
推荐阅读