Dropping temp tables

Dropping temp tables

Post by Steve Champea » Thu, 13 Nov 1997 04:00:00



Hello-

I would like to be able to drop temporary tables as soon as I am done
with them. Some research has revealed that temp tables are stored in the
tempdb database and SQL Server adds a numeric suffix to it. So if I call
a table

#TempTable2

the Tempdb.dbo.sysobjects table reveals that this table's full name is

#TempTable2_________0000000004

All efforts to drop this table has failed. What is the syntax?

I have tried all of the following, with no success:

DROP Tempdb.#TempTable2
DROP Tempdb.#TempTable2_________0000000004
DROP Tempdb.dbo.#TempTable2_________0000000004

Does anyone have any ideas?

Thanks a lot,

Steve C.

 
 
 

Dropping temp tables

Post by Terry Carm » Fri, 14 Nov 1997 04:00:00


On Wed, 12 Nov 1997 11:29:19 -0800, Steve Champeau


>Hello-

>I would like to be able to drop temporary tables as soon as I am done
>with them. Some research has revealed that temp tables are stored in the
>tempdb database and SQL Server adds a numeric suffix to it. So if I call
>a table

Temp tables are automatically dropped when the last user connection to
them terminates

If you created a local temp table (as #tablename), it vanishes when
your connection closes. If you crated a global temp table
(##tablename) it vanishes when the last user connection to it closes.

Terry

--------------------

Terry Carmen
Meadowbrook Software

NOTE TO SPAMMERS:
BAD THINGS will happen to any site that sends me email spam.

 
 
 

Dropping temp tables

Post by Erland Sommarsk » Sun, 16 Nov 1997 04:00:00



>If you created a local temp table (as #tablename), it vanishes when
>your connection closes.

And if you created the table in a stored procedure, the table disappears
when you exit the procedure.

--

F=F6r =F6vrigt anser jag att QP b=F6r f=F6rst=F6ras.
B=65sid=65s, I think QP should b=65 d=65stroy=65d.

 
 
 

1. Drop Temp Tables

Hello...

An algorithm drops a tempTable named CurrentUser + 'UnitHistory'.

It then re-creates it with the requested history for the unit selected.

My problem is that as users are added, if their is no table created with
that name....

the Drop table part of the stored proc fails.

My question is this.....
How do I check to see if a table exists so I can only execute the Drop table
if it exists.

Thanks in advance,
bob mcclellan

2. xp_unc_to_drive?

3. Drop temp tables in tempdb

4. the password of the internal

5. do we need to explicity drop temp tables in stored procedures

6. Transferring tables between servers

7. drop temp tables

8. LastWaitTypes: XCB & LCK_M_IS_U ???

9. Dropping #temp tables question

10. How to use a cursor to drop temp tables

11. HELP!! Can't drop temp table

12. Conditionally dropping temp tables

13. code to drop temp table that optionally exists