Dropping multiple tables with one DROP statement

Do you know that you can drop multiple tables using a sing DROP statement?

Assume that in your code or procedure you use many temporary tables and at the end you want to drop all of them. One method is to drop them one by one

Assume the following temporary tables

create table #t1(id int);
create table #t2(id int);
create table #t3(id int);

Usual method of dropping them is

drop table #t1
drop table #t1
drop table #t1

However, simply you can use single DROP statement with each table names seperated by comma

drop table #t1,#t2,#t3

How many of you know this?

Advertisements

4 thoughts on “Dropping multiple tables with one DROP statement

  1. Great post. If the temporary # tables are created, won’t it be dropped after the connection is closed. Please clarify

    1. Sai Prasad, as a best practice you need to drop all the temporary tables once the job is done. If they are created in a procedure, they are automatically dropped after the execution, But if you have them not part of procedure, the tables will exists until dropped manually or connection is closed. If the connection is open for long time and if there are many temporary tables, it may affect the performance of tempdb database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s