SQL Server 2016 shipped with a lot of TSQL Enhancements and one of them is DROP IF EXISTS. It actually minimizes the size of the script which you can use to drop any object (AGGREGATE, ASSEMBLY, COLUMN, CONSTRAINT, DATABASE, DEFAULT, FUNCTION, INDEX, PROCEDURE, ROLE, RULE, SCHEMA, SECURITY POLICY, SEQUENCE, SYNONYM, TABLE, TRIGGER, TYPE, USER, and VIEW). In other words, you DO NOT need to write long IF EXISTS Statements anymore to drop any object.
The beauty of this TSQL is that if it fails (in case, if object does not exist), it DOES NOT give you error message.
Lets me create a sample table to demonstrate how it works but as I mentioned earlier you can use this script to drop any object.
SAMPLE:
USE tempdb GO --Create Sample table CREATE TABLE dbo.tbl_Sample ( [ID] INT, [Name] NVARCHAR(50) ) GO
OLD METHOD:
Given below methods are compatible with SQL Server 2005 and above.
--Method 1
USE tempdb
GO
IF OBJECT_ID('dbo.tbl_Sample', 'U') IS NOT NULL
DROP TABLE dbo.tbl_Sample
GO
--Method 2
USE tempdb
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'tbl_Sample')
DROP TABLE dbo.tbl_Sample
GO
NEW METHOD:
Given below method is compatible with SQL Server 2016 and above.
USE tempdb GO DROP TABLE IF EXISTS tbl_Sample; GO
Reference : MSDN
SQL Server Portal 