You can also check for existence without using the objectID:

if exists (select * from TempDB.dbo.sysobjects where id = object_id(N'tempdb..[#MyTemp]') and type='U')
drop table [dbo].[#MyTemp]

CREATE TABLE #MyTemp (ColA varchar(30), ColB int)

Kevin Martin
Database Engineer
Multimedia Games, Inc.



-----Original Message-----
From: Horton, Joe (LNI) [mailto:[Email Address Removed] Monday, March 22, 2004 6:46 PM
To: LazyDBA.com Discussion
Subject: RE: #temp table object_id


Thanks - this was what I needed...lost myself migrating from Sybase!

__________________________
Joe Horton
Database Developer / Software Engineer
WISHA/Legal Services Software Development
Department of Labor and Industries
Voice (360) 902-5928 fax (360) 902-6200


-----Original Message-----
From: Duhn, Brian [mailto:Brian.[Email Address Removed]
Sent: Monday, March 22, 2004 4:40 PM
To: LazyDBA.com Discussion
Subject: RE: #temp table object_id

One step further and you're there:

CREATE TABLE #text (text_key VARCHAR (255), text_string TEXT NULL)
Select OBJECT_ID('tempdb..#text')


-----Original Message-----
From: dli [mailto:[Email Address Removed]
Sent: Monday, March 22, 2004 6:45 PM
To: LazyDBA.com Discussion
Subject: Re: #temp table object_id

CREATE TABLE #text (text_key VARCHAR (255), text_string TEXT NULL)
Select OBJECT_ID('#text')


Horton, Joe (LNI) writes:

> How come when I create a temp table as below, It does not show up in my
> OBJECT_ID? What I really need to do is test for the existence of a given
> temp table and drop it if it exists or create it if it doesn't.
>
> CREATE TABLE #text (text_key VARCHAR (255), text_string TEXT NULL)
> Select OBJECT_ID('#MyTempTable')
>
> RETURNS:
> NULL
>
> __________________________
> Joe Horton
> Database Developer / Software Engineer
> WISHA/Legal Services Software Development
> Department of Labor and Industries
> Voice (360) 902-5928 fax (360) 902-6200
>



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 2/10/2004

MS Sql Server LazyDBA home page