SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Function to Check the Existence of a Temp Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/13/2006 :  22:02:00  Show Profile  Reply with Quote
This 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.

The script creates the function and tests it. The expected test results are also included.

This was tested with SQL 2000 only.



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


Test Results:

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



CODO ERGO SUM

Edited by - Michael Valentine Jones on 06/14/2006 06:37:36

nvesic
Starting Member

Yugoslavia
1 Posts

Posted - 01/21/2011 :  09:57:12  Show Profile  Reply with Quote
Fine code! Also resolves problem where two sessions create temp table with sma name.

It works also on SQL 2005, when you make some minor changes. Instead of

select *
from
tempdb.dbo.sysobjects o
where
o.xtype in ('U') and
o.id = object_id( N'tempdb..'+@temp_table_name )


it should be:


select *
from
tempdb.sys.tables o
where
o.xtype in ('U') and
o.object_id = object_id( N'tempdb..'+@temp_table_name )
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000