How can I drop a table if it exists in SQL Server 2000?

Question

I have a DTS package that drops a table then creates it and populates it but sometimes something happens and the package fails after the drop table. If it's rerun it fails cuz the table hasn't been created yet.

Is there something like "if exists" for SQLServer 2000 like in MySQL?

thanks.

Edit Thanks everyone! I went with TrickyNixon because his was quick and easier to initiate. But, I would like everyone to eval that statement and make sure it is best-practices because if it is, I'm TOTALLY going to dump that into EVERY drop/create DTS package I see!!! Nice work TrickyNixon and everyone who threw in on this.

Answer 1

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TableName]') AND type in (N'U'))
DROP TABLE TableName;
GO

You can check a list of type definitions in the sys.objects table here if you want to check if other objects in your database exist.

Answer 2

You need to check the sysobjects table

Answer 3

The following works, just replace TABLENAME with your table

IF EXISTS( SELECT * FROM dbo.sysobjects where id = object_id(N'TABLENAME') AND OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
    DROP TABLE TABLENAME
END

Answer 4

Or quicker:

IF OBJECT_ID('temp_ARCHIVE_RECORD_COUNTS') IS NOT NULL  
  DROP TABLE temp_ARCHIVE_RECORD_COUNTS

Answer 5

Sure:

IF OBJECT_ID('YOURTABLENAME') IS NOT NULL

where YOURTABLENAME is whatever the name of your table is.

If it's a temp table, then just add tempdb.# before before the OBJECT_ID function call.

Answer 6

One thing to remember when you drop and object and then add back to the database is also add any permissions back to the table. This has tripped us up a number of times.

I up voted TracyNixon's answer. I would say you want to stay away from querying the sysobjects table directly because a Microsoft update could break that kind of code. You isolate yourself from that by using the OBJECT_ID function.

Answer 7

Noone has mentioned this method yet:

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME='MyTable') begin 
    drop table MyTable
end

This is the most portable method - it works on at least MSSQL2000 up to MSSQL2008.

The INFORMATION_SCHEMA tables are part of the SQL-92 standard.