Temporary table existence - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Temporary table existence

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-05 : 10:46:14

Roman writes "My multi user application (Access.adp) is connected to MS SQL server and I use some temorary tables e.g. as recordsource of form. When I use the temporary table first time, I must create and fill it, next time I must truncate and fill it. I have problem to get information if temporary table exist or not. I tried this query:
if not exists (select * from tempdb.dbo.sysobjects
where (name like N'#TableName[_][_][_]%') and (xtype = N'U'))
BEGIN .... END TRUNCATE ...
But the SELECT statement returns all tmp tables named "#TableName" in the database.
Please, exist some method to obtain information whether or not exists the temporary table created in my connection (session)?
Thanks!"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-05 : 11:35:39

to understand temp table life span read this:
http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx

Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-05 : 12:07:02

You can use function F_TEMP_TABLE_EXISTS to check if a temp table exists:

Function to Check the Existence of a Temp Table
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67736

CODO ERGO SUM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-06 : 15:27:54

As others have mentioned there are various ways to check for the existance of a temp table. I usually use the following code to check if a table exists or not.

IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL 
    PRINT '#MyTable exists.' 
ELSE 
    PRINT '#MyTable does not exist.'

Cheers,

-Ryan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-06 : 16:09:13

quote:
Originally posted by Lamprey

As others have mentioned there are various ways to check for the existance of a temp table. I usually use the following code to check if a table exists or not.
IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL
PRINT '#MyTable exists.'
ELSE
PRINT '#MyTable does not exist.'

Cheers,

-Ryan

The problem with that code is that it doesn't verify that the object is actually a table:


create proc #MyTemp as select 1
go
IF OBJECT_ID('tempdb..#MyTemp') IS NOT NULL 
    drop table #MyTemp

Results:


Server: Msg 3705, Level 16, State 1, Line 2
Cannot use DROP TABLE with '#MyTemp' because '#MyTemp' is a procedure. Use DROP PROCEDURE.

CODO ERGO SUM

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-03-08 : 22:29:27

But this does...


     IF OBJECTPROPERTY(OBJECT_ID('TempDB..#sometemptable'),'IsTable') = 1
        DROP TABLE #sometemptable

--Jeff Moden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-03-09 : 08:48:00

quote:
Originally posted by Jeff Moden

But this does...

IF OBJECTPROPERTY(OBJECT_ID('TempDB..#sometemptable'),'IsTable') = 1
DROP TABLE #sometemptable

--Jeff Moden

That code only works if you are connected to the tempdb database.

If you are in a different database, the OBJECTPROPERTY function will return a NULL.

These issues are why I wrote the F_TEMP_TABLE_EXISTS function.


use pubs

create table #sometemptable ( x int)

select IsTable = OBJECTPROPERTY(OBJECT_ID('TempDB..#sometemptable'),'IsTable')

use tempdb

select IsTable = OBJECTPROPERTY(OBJECT_ID('TempDB..#sometemptable'),'IsTable')

drop table #sometemptable

Results:


IsTable     
----------- 
NULL

(1 row(s) affected)

IsTable     
----------- 
1

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources