Advertisement
Advertisement


How to drop a table if it exists?


Question

The table name is Scores.

Is it correct to do the following?

IF EXISTS(SELECT *
          FROM   dbo.Scores)
  DROP TABLE dbo.Scores
2019/10/04
1
752
10/4/2019 3:03:00 PM

Accepted Answer

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; 

SQL Server 2016+ has a better way, using DROP TABLE IF EXISTS …. See the answer by @Jovan.

2018/09/16
1419
9/16/2018 12:55:23 PM


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.

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).

2015/10/13

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

The only code that works is:

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

In SQL Server 2016 (13.x) and above

DROP TABLE IF EXISTS dbo.Scores

In earlier versions

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

U is your table type

2018/10/16

Or:

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

I hope this helps:

begin try drop table #tempTable end try
begin catch end catch
2014/09/26

Source: https://stackoverflow.com/questions/7887011
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]