清楚临时表、查看临时表占用内存

最新推荐文章于 2022-08-17 10:39:38 发布

苦茶折柳

最新推荐文章于 2022-08-17 10:39:38 发布

阅读量153

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_51803498/article/details/125756209

版权

 --登记表记录
select * from T_BAS_TEMPORARYTABLENAME where FPROCESSTYPE=1 or FCREATEDATE<GETDATE()-1 
delete from T_BAS_TEMPORARYTABLENAME where FPROCESSTYPE=1 or FCREATEDATE<GETDATE()-1


--删除临时表
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=1select 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 create_date<=DATEADD(n,-5, 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   exec(@sql)   set @sql='drop table 'end
if object_id('temptb','table')>0 drop table temptb;


 --查看占用内存
select cast(sum(a.total_pages)*8/1024 as varchar)+' MB' total		 
from sys.partitions p 
join sys.allocation_units a on p.partition_id = a.container_id 
join sys.tables it on p.object_id = it.object_id
where it.name like 'TMP%'