CODEWITHSUNDEEP
sql-servertemp-tables
rvphx
rvphx

Reputation: 2402

IF EXISTS does not drop the temp table

This is probably a multipart question:

When I issue the statment 

IF OBJECT_ID('temp..#tablename) IS NOT NULL --drop the table here

it does not drop the temp table. When I go look at the temp db, the name is something totally different...

#tablename___________________________________________00000001069F

Obviously, the drop statement wont work with this. How can I make sure that the temp table gets dropped with the above statement.

Also, if I use the "USE dbName" before the create temp table statement, does the temp table still get created in tempdb or the the dbName database? Is the default always tempDb?

Thanks, RV.

Upvotes: 0

Views: 5995

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28890

use should use 

OBJECT_ID('tempDB..#tablename')

not

OBJECT_ID('temp..#tablename')

does the temp table still get created in tempdb or the the dbName database? Is the default always tempDb?

YES

Temp Tables always gets created in TEMPDB..

Reason why you are seeing name like _____00000001069F is due to the fact that Temp tables are session specific and SQL takes care of assigning names to them,so that names won't conflict,even when they are used in parallel sessions with same names

Look out this answer on DBA.SE for more info,specifically look out Temp Tables section :

What's the difference between a temp table and table variable in SQL Server?

Upvotes: 7

Related Questions