if objectproperty(object_id('dbo.F_TEMP_TABLE_EXISTS'),'IsScalarFunction') = 1
	begin drop function dbo.F_TEMP_TABLE_EXISTS end
go
create function  dbo.F_TEMP_TABLE_EXISTS
	( @temp_table_name sysname )
returns int
as
/*
Function: F_TEMP_TABLE_EXISTS

	Checks for the existence of a temp table
	(## name or # name), and returns a 1 if
	it exists, and returns a 0 if it doesn't exist.

*/
begin

if exists (
	select  *
	from
		tempdb.dbo.sysobjects o
	where
		o.xtype in ('U')	and
		o.id = object_id( N'tempdb..'+@temp_table_name )
	)
	begin return 1 end

return 0

end
go
print 'Create temp tables for testing'
create table #temp (x int)
go
create table ##temp2 (x int)
go
print 'Test if temp tables exist'

select
	[Table Exists] = dbo.F_TEMP_TABLE_EXISTS ( NM ),
	[Table Name] = NM
from
	(
	select nm = '#temp' union all
	select nm = '##temp2' union all
	select nm = '##temp' union all
	select nm = '#temp2'
	) a


print 'Check if table #temp exists'

if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) =  1
	print '#temp exists'
else
	print '#temp does not exist'


print 'Check if table ##temp4 exists'
if dbo.F_TEMP_TABLE_EXISTS ( '##temp4' ) =  1
	print '##temp4 exists'
else
	print '##temp4 does not exist'
go

-- Drop temp tables used for testing,
-- after using function F_TEMP_TABLE_EXISTS
-- to check if they exist.

if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) = 1
	begin 
	print 'drop table #temp'
	drop table #temp
	end

if dbo.F_TEMP_TABLE_EXISTS ( '##temp2' ) = 1
	begin
	print 'drop table ##temp2'
	drop table ##temp2
	end

Create temp tables for testing
Test if temp tables exist
Table Exists Table Name 
------------ ---------- 
1            #temp
1            ##temp2
0            ##temp
0            #temp2

(4 row(s) affected)

Check if table #temp exists
#temp exists
Check if table ##temp4 exists
##temp4 does not exist
drop table #temp
drop table ##temp2