--检查已标记为需要删除的临时表

select * from

T_BAS_TEMPORARYTABLENAME;

--所有系统创建的临时表及视图

SELECT * FROM sys.tables

WHERE name LIKE 'TMP%' 

-- 查看系统所有表占用的空间情况

create table tmpspace

(Fname varchar(50),

Frows int,

Freserved varchar(50),

  Fdata

varchar(50),

  Findex_size varchar(50),

  Funused

varchar(50));

 

insert into tmpspace

(Fname,Frows,Freserved,  Fdata,Findex_size,Funused)

exec sp_msforeachTable

@Command1="sp_spaceused '?'"

select * from tmpspace where Fname like

'TMP%' order by Fdata desc

--临时表占用的总大小（M）

select

SUM(CONVERT(DECIMAL,replace(fdata,'KB','')))/1024 M from tmpspace where Fname

like 'TMP%';

--drop table tmpspace;

--删除所有已经标记为需要删除的临时表

declare

@sql as varchar(max)

set @sql=''

select @sql=@sql+'drop table '+name+';'

from sys.tables u

join T_BAS_TEMPORARYTABLENAME v on u.name=v.FTABLENAME and

( v.FPROCESSTYPE=1 or

v.FCREATEDATE<GETDATE()-1);

exec(@sql);

delete u from

T_BAS_TEMPORARYTABLENAME u where

not exists(select 1 from sys.tables where

u.ftablename=name );