Checking for existence of a temp table before droping it

[nduckste]

I'm familiar with how to check for the existence of a table before dropping it using the following command:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[xxx]

How does one check for the existence of a temp table (using # syntax) before dropping it? I've tried various flavors of this command and none work. One flavor is

use tempdb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#xxx]

CREATE TABLE #xxx (
NumID INTEGER IDENTITY(1,1),
Exhibitor_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL
)

Thanks.

Nick

[skhanal]

SQL Server uniquifies temp table names so sysobjects will not have an entry for #xxx, it may be #xxx_____something.

so check the object id, if it is not null then you can drop it

If object_id(#xxx) is not null
drop table #xxx

[nduckste]

Worked great thanks.