--登记表记录select*from T_BAS_TEMPORARYTABLENAME where FPROCESSTYPE=1or FCREATEDATE<GETDATE()-1deletefrom T_BAS_TEMPORARYTABLENAME where FPROCESSTYPE=1or FCREATEDATE<GETDATE()-1
--删除临时表if object_id('temptb','table')>0droptable temptb;declare@sqlvarchar(max)declare@icountintdeclare@Iintset@sql='drop table 'set@i=1select name,IDENTITY(int,1,1) id into temptb from sys.tables t
where name like'tmp%'andnotexists(select1from T_BAS_TEMPORARYTABLENAME where FTABLENAME=t.name)and create_date<=DATEADD(n,-5, GETDATE())select@icount=@@ROWCOUNTwhile@i<@icountbeginselect@sql=@sql+name+','from temptb
where id between@iand@i+49if @@ROWCOUNT>0set@sql=substring(@sql,1,len(@sql)-1)+';'set@i=@i+50exec(@sql)set@sql='drop table 'endif object_id('temptb','table')>0droptable temptb;
--查看占用内存select cast(sum(a.total_pages)*8/1024asvarchar)+' 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%'