"S" SQL Server checks for temporary table space usage

--Check for temporary tables that have been marked as needing to be deleted
SELECT * FROM
T_bas_temporarytablename;

--Temporary tables and views created by all systems
SELECT * from Sys.tables
WHERE name like ' tmp% '

--View the space occupied by all the tables in the system
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

--Total size occupied by temporary tables (M)
Select
SUM (CONVERT (Decimal,replace (fdata, ' KB ', '))/1024 M from Tmpspace where Fname
Like ' tmp% ';
--drop table Tmpspace;

--Delete all temporary tables that have been marked as needing to be deleted
Declare
@sql as varchar (max)
Set @sql = ' '
Select @[email protected]+ ' 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);

"S" SQL Server checks for temporary table space usage