如果存在SQL服务器，如何删除表?

The table name is Scores.

表名是分数。

Is it correct to do the following?

这样做对吗?

IF EXISTS(SELECT *
          FROM   dbo.Scores)
  DROP TABLE dbo.Scores

13 个解决方案

#1

1013

Is it correct to do the following?

这样做对吗?
IF EXISTS(SELECT *
          FROM   dbo.Scores)
  DROP TABLE dbo.Scores

No. That will drop the table only if it contains any rows (and will raise an error if the table does not exist).

不。只有当它包含任何行时才会删除表(如果表不存在，则会引发错误)。

Instead, for a permanent table you can use

相反，您可以使用一个永久的表。

IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL 
  DROP TABLE dbo.Scores;

Or, for a temporary table you can use

或者，您可以使用一个临时表。

IF OBJECT_ID('tempdb.dbo.#T', 'U') IS NOT NULL
  DROP TABLE #T;

For anyone on SQL Server 2016+ I'll point out this answer - which has a better way.

对于SQL Server 2016+的任何人，我将指出这个答案——它有更好的方法。

#2

161

From SQL Server 2016 you can use

您可以使用SQL Server 2016。

DROP TABLE IF EXISTS dbo.Scores

Reference: DROP IF EXISTS - new thing in SQL Server 2016

引用:在SQL Server 2016中，如果存在新东西，就删除它。

It will be in SQL Azure Database soon.

它将很快出现在SQL Azure数据库中。

#3

123

The ANSI SQL/cross-platform way is to use the INFORMATION_SCHEMA, which was specifically designed to query meta data about objects within SQL databases.

ANSI SQL/跨平台的方法是使用INFORMATION_SCHEMA，它是专门用来查询SQL数据库中对象的元数据的。

if exists (select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'Scores' AND TABLE_SCHEMA = 'dbo')
    drop table dbo.Scores;

Most modern RDBMS servers provide, at least, basic INFORMATION_SCHEMA support, including: MySQL, Postgres, Oracle, IBM DB2, and Microsoft SQL Server 7.0 (and greater).

大多数现代RDBMS服务器至少提供基本的INFORMATION_SCHEMA支持，包括:MySQL、Postgres、Oracle、IBM DB2和Microsoft SQL Server 7.0(以及更大的)。

#4

Have seen so many that don't really work. when a temp table is created it must be deleted from the tempdb!

见过这么多不怎么管用的东西。当创建临时表时，必须从tempdb中删除它!

The only code that works is:

唯一有效的代码是:

IF OBJECT_ID('tempdb..#tempdbname') IS NOT NULL     --Remove dbo here 
    DROP TABLE #tempdbname   -- Remoeve "tempdb.dbo"

#5

Or:

或者:

if exists (select * from sys.objects where name = 'Scores' and type = 'u')
    drop table Scores

#6

I wrote a little UDF that returns 1 if its argument is the name of an extant table, 0 otherwise:

我写了一个UDF，如果它的参数是一个extant表的名称，则返回1，否则:

CREATE FUNCTION [dbo].[Table_exists]
(
    @TableName VARCHAR(200)
)
    RETURNS BIT
AS
BEGIN
    If Exists(select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = @TableName)
        RETURN 1;

    RETURN 0;
END

GO

To delete table User if it exists, call it like so:

若要删除表用户，请将其命名为:

IF [dbo].[Table_exists]('User') = 1 Drop table [User]

#7

I hope this helps:

我希望这有助于:

begin try drop table #tempTable end try
begin catch end catch

#8

Simple is that:

简单的是:

IF OBJECT_ID(dbo.TableName, 'U') IS NOT NULL
DROP TABLE dbo.TableName

where dbo.TableName is your desired table and 'U' is type of your table.

dbo的地方。TableName是您想要的表，“U”是您的表的类型。

#9

DROP TABLE IF EXISTS dbo.Scores

或

IF OBJECT_ID('dbo.Scores', 'U') IS NOT NULL 
DROP TABLE dbo.Scores;

U is your table type

U是你的表格类型。

#10

IF EXISTS (SELECT NAME FROM SYS.OBJECTS WHERE object_id = OBJECT_ID(N'Scores') AND TYPE in (N'U'))
    DROP TABLE Scores
GO

#11

I use:

我使用:

if exists (select * 
           from sys.tables 
           where name = 'tableName' 
           and schema_id = schema_id('dbo'))
begin
    drop table dbo.tableName
end

#12

-1

Use this command, I have been using this..

使用这个命令，我一直在用这个。

IF OBJECT_ID('mytablename') IS NOT NULL 
BEGIN
    DROP TABLE mytablename 
END

#13

-5

Do like this, it is the easiest way.

这样做，这是最简单的方法。

qry will be your own query, whatever you want in the select list.

qry将是您自己的查询，无论您在选择列表中需要什么。

set @qry = ' select * into TempData from (' + @qry + ')Tmp  '

exec (@qry)

select * from TempData 

drop table TempData

#1