Drop a Temp Table

Using SQL Server 2005. I have a stored prod that uses a temp table. I need to test at the start of the prod to see if the temp table is there. Using the following code at the start of my prod, but does not run.

IF exists(select * from ##TO_STATUS_TBL)
DROP TABLE ##TO_STATUS_TBL

What is the best way to check and/or drop the temp table.
Thank you, DavidYou do not need to test if the temporary table is there. Temporary tables exist only within the scope of the procedure, and are automatically dropped when it completes. (OK, the exception would be global temporary tables, but you probably shouldn't be using those anyway...).|||Looks like a global temporary table. I am not sure if it is strictly required, but if it is, how do you determine if other processes still need it?

Anyway. This code should drop it every time

if exists (select * from tempdb..sysobjects where name like '##TO_STATUS_TBL%')
begin
drop table ##TO_STATUS_TBL
end

If I were you, I would look into whether the global part of the temp table is really needed. And if not, make this a local temp table.

EDIT: Fixed typo in code.|||Thank you all for your help. Did not see that I was using a globe temp table. Changed code so that I am not using a globe table. Code runs fine. David|||I usually do the following:

if object_id( '#table' ) is not null
begin
drop table #table
end|||You do not need to test if the temporary table is there. Temporary tables exist only within the scope of the procedure, and are automatically dropped when it completes. (OK, the exception would be global temporary tables, but you probably shouldn't be using those anyway...).

As far my knowledge says, that the scope of the #TempTable is for the Connection....... Not restricted to the SP only. Hence even if the SP is executed, #TempTable will remain there in the temp DB until and unless the DB connection is lost. With the same DB connection, I can run many SPs one after another, and the #TempTable will remain as it is till either will explicitly drop it or we loose the DB connection.

Thanks,
Rahul Jha|||No Guys, I was wrong....... You were right BlindMan

Thanks,
Rahul Jha|||I usually do the following:

if object_id( '#table' ) is not null
begin
drop table #table
end
Shouln't this be:
if object_id( 'tempdb..#table' ) is not null
...|||Shouln't this be:
if object_id( 'tempdb..#table' ) is not null
...

I believe a table preceded by the '#' will always be assumed to be in tempdb, as user sprocs with 'sp_' are always checked to be in master.|||As far my knowledge says, that the scope of the #TempTable is for the Connection....... Not restricted to the SP only. Hence even if the SP is executed, #TempTable will remain there in the temp DB until and unless the DB connection is lost. With the same DB connection, I can run many SPs one after another, and the #TempTable will remain as it is till either will explicitly drop it or we loose the DB connection.

Thanks,
Rahul Jha

Temp table #TempTable will have a name like #TempTable______2344237748 in tempdb..sysobjects.|||I believe a table preceded by the '#' will always be assumed to be in tempdb, as user sprocs with 'sp_' are always checked to be in master.
True, but unfortunately not for the function OBJECT_ID. If you run the test below you will see that the first statement returns NULL and the second the object id.

CREATE TABLE #tst (c INT)

SELECT OBJECT_ID('#tst')
UNION
SELECT OBJECT_ID('tempdb..#tst')

DROP TABLE #tst