--当SQL Server数据库使用时间长了，会产生很多临时表，此时需要清除临时表，能对数据库性能有较大提高

/*清除临时表数据，清除指定日期前的数据*/

if object_id('temptb','table')>0 drop table temptb;

declare @sql varchar(max)

declare @icount int

declare @I int

declare @day int

set @day =-5/*清除临时表数据，清除指定日期前的数据 -5表示1天前的数据*/

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   exists(select 1 from T_BAS_TEMPORARYTABLENAME where FTABLENAME=t.name and  FPROCESSTYPE=1 OR 

(FCREATEDATE <= DATEADD(n, @day, GETDATE())and  FPROCESSTYPE=0)) and create_date<=DATEADD(n, @day, 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

DELETE from  T_BAS_TEMPORARYTABLENAME  WHERE EXISTS( SELECT 1 FROM temptb WHERE temptb.name = T_BAS_TEMPORARYTABLENAME.FTABLENAME);

--select * from  T_BAS_TEMPORARYTABLENAME  WHERE EXISTS( SELECT 1 FROM temptb WHERE temptb.name = T_BAS_TEMPORARYTABLENAME.FTABLENAME AND 1<>1);

if object_id('temptb','table')>0 drop table temptb;