This works, unless you use #temp table names with more than two subsequent underscores or ending with an underscore:

CREATE PROCEDURE [dbo].[dtt]

AS

-- drop all #temp tables for current session

begin

DECLARE @sql VARCHAR(60),

@name VARCHAR(60)

DECLARE dtt CURSOR

FOR SELECT SUBSTRING(t.name, 1, CHARINDEX('___', t.name) - 1)

FROM tempdb.sys.tables AS t

WHERE t.name LIKE '#%[_][_][_]%'

AND t.[object_id] = OBJECT_ID('tempdb..' + SUBSTRING(t.name, 1, CHARINDEX('___', t.name) - 1))

open dtt

fetch next from dtt into @name

while @@fetch_status <> -1

BEGIN

SELECT @sql = 'DROP TABLE ' + @name

EXEC ( @sql )

fetch next from dtt into @name

END

CLOSE dtt

deallocate dtt

END

It works because OBJECT_ID returns NULL for #temp tables that belong to other sessions.