How to Check if a Temporary Table Exists

Ordinarily if you want to create or reference a temporary table you do not need to use the database name to reference it eg dbname..#tablename.  Usually having a hash # at the beginning of the table name is enough for sql server to know it is a temporary table.
 
So for example the following statements will run without error:

• create table #mytable(col1 int)
• insert into #mytable values(2)
• delete from #mytable
• drop table #mytable

Try running this statement which checks for the existence of a temporary table and drops it if it does exist:

if OBJECT_ID('#mytable') is not null drop table #mytable
create table #mytable(col1 varchar(10))

It runs without error but if you try running it again you will get the following error:

Msg 2714, Level 16, State 6, Line 2
There is already an object named '#mytable' in the database. 

So the check for the temporary table has not done what we expected and the table wasn't dropped.  You need to add a reference to the tempdb in the statement for it to work:

if OBJECT_ID('tempdb..#mytable') is not null drop table #mytable
create table #mytable(col1 varchar(10))

The statement now runs successfully. If however you were to add the database reference to both occurances of the table like so:
if OBJECT_ID('tempdb..#mytable') is not null drop table tempdb..#mytable
create table #mytable(col1 varchar(10))

Then this will produce the following error:
Database name 'tempdb' ignored, referencing object in tempdb.
 
You do not need to put the reference into the drop table part of the statement.