Trying to check for the existence of a table

Question

Trying to check for the existence of a table

ducksql@gmail.com

SSCrazy

Points: 2826
More actions
April 1, 2011 at 1:06 pm

#74663

I am trying to use this code I picked up to check for the existence of a table, but it doesn't seem to work right. Could someone please help out?
IF EXISTS (SELECT * from sysobjects
WHERE id = object_id('[DATABASE].[dbo].[TableName]') AND
OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE TableName
Thank you.
Steve

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Compared to the database you're running this code from, where is the table to be dropped? If it's in another database, then OBJECT_ID isn't going to work as you might expect it to.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ducksql@gmail.com SSCrazy Points: 2826 More actions · Answer 2

I am pulling from the same database. Basically what I am doing is creating a temp table in a sproc. Before I do I want to make sure the old one has been deleted.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Ah... ok. From the code example you gave, I'm not sure if you're trying to drop an actual Temp Table or an expendible table in the current database. One of the two following methods should do the trick just fine (I use them all the time)...

--===== This will conditionally drop a table from the current database

IF OBJECT_ID('dbo.SomeTableName','U') IS NOT NULL

DROP dbo.SomeTableName

;

--===== This will conditionally drop a Temp Table for the current scope

IF OBJECT_ID('tempdb..#SomeTempTableName','U') IS NOT NULL

DROP #SomeTempTableName

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)