Find leaking temp tables in MSSQL

find_leaking_temp_tables.sql

SELECT TOP 1 * 
INTO  #foo
FROM T_Benutzer
;

IF OBJECT_ID('tempdb..#NOT_foo') IS NOT NULL DROP TABLE [#foo]; -- this is presumed buggy

-- Find leaking temp-tables 

SELECT 
     -- Strip the mangled suffix to get the original name 
	 SUBSTRING(name, 1, CHARINDEX('_', name + '_', 2) - 1) AS temp_table_name 
	,name AS internal_name 
	,create_date 
-- FROM tempdb.sys.objects 
FROM tempdb.sys.tables 
WHERE type = 'U' 
AND name LIKE '#%' 
-- the function OBJECT_ID is session-scoped 
AND OBJECT_ID('tempdb..' + QUOTENAME(name)) IS NOT NULL 
-- tempdb.. is the same as tempdb.dbo. 
-- The .. means "default schema" 
-- AND OBJECT_ID('tempdb.dbo.' + QUOTENAME(name)) IS NOT NULL 
ORDER BY create_date;