How do I check if #tempTable exists?

Transact-SQL

Seidel1


Is there a way in T-SQL to check to see if a #tempTable exists I want to write a proc the uses a temp table, but I first need to see if the table already exists. if it does I want to drop it, otherwise skip


Top

Re: How do I check if #tempTable exists?

Adamus Turner


The life of a #temp table is very short.

If the #temp table is not in your stored procedure, it doesn't exist. It is dropped when the procedure it resides in completes.

Adamus






Top

Re: How do I check if #tempTable exists?

JimSeidel

I realize that it is short lived. Is each call from an ASP page considered a different life, i.e. if I use a postback on my page that uses a #temp table, will that temp table be dropped after EACH postback




Top

Re: How do I check if #tempTable exists?

DaleJ

Code Snippet

if exists (
select * from tempdb.dbo.sysobjects o
where o.xtype in ('U')

and o.id = object_id(N'tempdb..#tempTable')
)
DROP TABLE #tempTable;





Top

Re: How do I check if #tempTable exists?

DaleJ

A local temp table lives for the life of a connection.
A global temp table lives for the life of all connections referencing it.




Top

Re: How do I check if #tempTable exists?

hunchback

Try:

if object_id('tempdb..#t1') is not null

drop table #t1

AMB


Top

Re: How do I check if #tempTable exists?

Umachandar Jayachandran - MS

There is no reason to check for #tempTable if you are creating the temporary table within a SP and using it there. Local temporary tables (#something) are scoped to the batch / session / module. SQL Server 2000 onwards allows you to reference say a temporary table created in a SP inside a trigger or other SPs called from the main one. Global temporary tables are instance specific so you will have to serialize creation/deletion etc. In any case, you can use OBJECT_ID function to check for temporary tables. Specify the temporary table using 3-part name like:
if OBJECT_ID('tempdb..#test') is not null
--- temp table exists




Top