如果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.

我有一个DTS包，删除一个表然后创建它并填充它但有时会发生一些事情，并且包在drop table之后失败。如果它重新运行则失败，因为尚未创建表。

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

像MySQL这样的SQLServer 2000有“if exists”吗？

thanks.

谢谢。

7 个解决方案

#1

Or quicker:

或者更快：

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

OBJECT_ID - MSDN Reference - SQL Server 2000
OBJECT_ID - MSDN参考 - SQL Server 2000
OBJECT_ID - MSDN Reference - SQL Server 2008
OBJECT_ID - MSDN参考 - SQL Server 2008

#2

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.

如果要检查数据库中是否存在其他对象，可以在此处检查sys.objects表中的类型定义列表。

#3

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.

这是最便携的方法 - 它至少可以在MSSQL2000上运行到MSSQL2008。

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

INFORMATION_SCHEMA表是SQL-92标准的一部分。

#4

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.

我投了TracyNixon的答案。我想说你想远离直接查询sysobjects表，因为Microsoft更新可能会破坏这种代码。您可以使用OBJECT_ID函数将自己与之隔离开来。

#5

Sure:

当然：

IF OBJECT_ID('YOURTABLENAME') IS NOT NULL

如果OBJECT_ID（'YOURTABLENAME'）不是NULL

where YOURTABLENAME is whatever the name of your table is.

其中YOURTABLENAME是你桌子的名字。

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

如果它是临时表，那么只需在OBJECT_ID函数调用之前添加tempdb。＃。

#6

You need to check the sysobjects table

您需要检查sysobjects表

#7

The following works, just replace TABLENAME with your table

以下工作，只需将TABLENAME替换为您的表

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

#1