When you need to delete a SQL Server table, you can use the DROP TABLE statement.
However, this statement will only work if the table you're trying to delete doesn't have any dependencies. If there are any objects that depend on the table you're trying to delete, you'll need to first delete or remove those dependencies.

If the table you're trying to delete doesn't exist, SQL Server will just ignore the statement and move on. This is why it's important to use the SQL Server drop table if exists syntax when you're working with temporary tables; otherwise, you may get an error message if the table you're trying to delete doesn't exist.

It's also important to note that the SQL Server drop table if exists syntax only works with tables. You can't use it to delete other objects, such as views or stored procedures. Attempting to do so will result in an error message.

Syntax

The SQL Server drop table if exists syntax is very simple. It's just the DROP TABLE keyword followed by the name of the table you want to delete.

DROP TABLE [ IF EXISTS ] table_name;

Example

For example, if you wanted to delete a table named "emp" you would use the following T-SQL statements:
CREATE TABLE emp(id int, ename varchar(50));
DROP TABLE IF EXISTS emp;

OBJECT_ID()

The following example creates a table, tests for its existence with OBJECT_ID() metadata function and then drop the table.

CREATE TABLE emp(id int, ename varchar(50));
IF OBJECT_ID('dbo.emp', 'U') IS NOT NULL
DROP TABLE dbo.emp;

Drop table using IF EXISTS with SELECT

The following example creates a table in SQL Server database, insert a row in table, tests for its existence with IF EXISTS and if the exists find a row in the table then drop the table.

CREATE TABLE emp(id int, ename varchar(50));
INSERT INTO emp(id, ename) values (1, 'Ben');
IF EXISTS(SELECT * FROM dbo.emp)
DROP TABLE dbo.emp;

Drop a temporary table in SQL Server

One common situation where you may need to use the SQL Server drop table if exists syntax is when you are trying to delete a temporary table. Temporary tables are automatically dropped when the session that created them ends. However, if you want to delete a temporary table before the session ends, you'll need to use the DROP TABLE IF EXISTS syntax.

For example, if you wanted to delete a table named "temp" you would use the following T-SQL statements:
CREATE TABLE #temp(id int, ename varchar(50));
GO
INSERT INTO #temp VALUES (1, 'Ben');
GO
SELECT * FROM #temp;
GO
DROP TABLE IF EXISTS #temp;
GO