如果 SQL Server 2000 中存在表，如何删除该表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/251181/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I drop a table if it exists in SQL Server 2000?
提问by Keng
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 包，它删除一个表，然后创建它并填充它，但有时会发生一些事情并且包在删除表后失败。如果重新运行它会失败，因为尚未创建表。
Is there something like "if exists" for SQLServer 2000 like in MySQL?
SQLServer 2000 中是否有类似于 MySQL 中的“如果存在”之类的东西？
thanks.
谢谢。
回答by Bob Probst
Or quicker:
或者更快：
IF OBJECT_ID('temp_ARCHIVE_RECORD_COUNTS') IS NOT NULL
DROP TABLE temp_ARCHIVE_RECORD_COUNTS
回答by Brian R. Bondy
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 hereif you want to check if other objects in your database exist.
您可以检查在sys.objects中表这里的类型定义列表，如果你想检查是否存在于数据库中的其他对象。
回答by Blorgbeard is out
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 标准的一部分。
回答by user8605
Sure:
当然：
IF OBJECT_ID('YOURTABLENAME') IS NOT NULL
IF OBJECT_ID('YOURTABLENAME') IS NOT NULL
where YOURTABLENAMEis whatever the name of your table is.
这里YOURTABLENAME是无论你的表的名称是。
If it's a temp table, then just add tempdb.#before before the OBJECT_IDfunction call.
如果它是一个临时表，那么只需tempdb.#在OBJECT_ID函数调用之前添加。
回答by Mike Daniels
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.
我对特雷西尼克松的回答投了赞成票。我会说您不想直接查询 sysobjects 表，因为 Microsoft 更新可能会破坏这种代码。您可以使用 OBJECT_ID 函数将自己与此隔离开来。
回答by Micha? Piaskowski
You need to check the sysobjectstable
您需要检查sysobjects表
回答by Mitchel Sellers
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
