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 

Answer 1

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.#TempTableName', 'U') IS NOT NULL   DROP TABLE #TempTableName;  

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

Answer 2

From SQL Server 2016 you can use

DROP TABLE IF EXISTS dbo.Scores 

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

It will be in SQL Azure Database soon.