CODEWITHSUNDEEP
sqlsql-server
Keng
Keng

Reputation: 53101

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

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.

Upvotes: 20

Views: 35190

Answers (7)

user8605
user8605

Reputation: 31

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.

Upvotes: 3

Blorgbeard
Blorgbeard

Reputation: 103447

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.

Upvotes: 8

Bob Probst
Bob Probst

Reputation: 9641

Or quicker:

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

Upvotes: 32

Brian R. Bondy
Brian R. Bondy

Reputation: 347216

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.

Upvotes: 10

Mitchel Sellers
Mitchel Sellers

Reputation: 63126

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

Upvotes: 1

Mike Daniels
Mike Daniels

Reputation: 245

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.

Upvotes: 3

Michał Piaskowski
Michał Piaskowski

Reputation: 3840

You need to check the sysobjects table

Upvotes: 1

Related Questions