DROP multiple tables using a single DROP statement

The DROP TABLE command can also be used to drop multiple tables in a single statement.

Example,

CREATE TABLE #customerprofile
(
cusId INT,
cusname VARCHAR(50),
CONSTRAINT PK_cusid_customerprofile PRIMARY KEY(cusId)
);
CREATE TABLE #customeraddress
(
cusId INT,
addressType CHAR(1),
gpslocation VARCHAR(100)
)

Separate each table with a comma in the DROP TABLE statement to drop multiple tables

DROP TABLE #customerprofile, #customeraddress

Starting from SQL Server 2016, the IF EXISTS can be specified in the DROP TABLE command

DROP TABLE IF EXISTS #customerprofile, #customeraddress

It is a common practice to use temporary tables in a stored procedure and drop them explicitly using the DROP TABLE command. Writing the DROP TABLE statement of every table can be cumbersome when there are many tables to be dropped. The option of specifying multiple tables in a single command will boost the code brevity and reduce the no of lines in the code.