Check if table exists then delete it in SQL Server

If you are using SQL Server older than 2016, you can use below query to check if table exists and then drop it

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

The above will drop table if it contains Rows and table exists, otherwise it will throw error.

OR

IF OBJECT_ID(N'dbo.TableName', N'U') IS NOT NULL  
   DROP TABLE [dbo].[TableName];  
GO

For Newer version (SQL Server 16+, Version 13.X), you can use below query (Works in SQL Azure database also)

DROP TABLE IF EXISTS dbo.YourTableName

Hope it helps.

There are multiples ways to check if table exists in database using SQL

Using ObjectId we can use below SQL query

IF OBJECT_ID(N'dbo.TableName', N'U') IS NOT NULL
BEGIN
    PRINT 'Table Exists'
END

Using sys.Tables 

 IF EXISTS(SELECT 1 FROM sys.Tables WHERE  Name = N'TableName' AND Type = N'U')
 BEGIN
      PRINT 'Table Exists'
 END

Using INFORMATION_SCHEMA.TABLES view

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'TableName')
BEGIN
    PRINT 'Table Exists'
END

But if you want to check if table exits and delete it, then simplest would be to use

drop table if exists TableName

But this query only works starting from SQL Server 2016