How to check if temporary / permanent objects exists and drop if it exists before creating them

There are many ways to perform drop the existing objects before create them. One of the common way use OBJECT_ID as shown below.

--for a permanent table you can use

IF OBJECT_ID('dbo.tabl1', 'U') IS NOT NULL

 DROP TABLE dbo.tabl1;

--for a temporary table you can use

IF OBJECT_ID('tempdb.dbo.#tbl1', 'U') IS NOT NULL

 DROP TABLE #tbl1;

In SQL Server 2016, new function DROP IF EXISTS introduced to perform this task. Currently, the following objects can DIE.

--from SQL Server 2016 and later

DROP PROCEDURE IF EXISTS

dbo.tabl1, dbo.tab2, dbo.tab3, dbo.tab4, dbo.tab5, #tbl1;

--to drop Stored Procedure

DROP PROCEDURE IF EXISTS

dbo.sp1, dbo.sp2;

Cheers!

Uma