sql-servert-sql

How to drop a table if it exists?


The table name is Scores.

Is it correct to do the following?

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

Solution

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