Recently I had a very interesting conversation with one of my clients when I was working on Comprehensive Database Performance Health Check. My client was creating many temporary and real tables in their query logic. When the tables were not needed they were dropping them one at a time, which was taking time as well as require to write them one at a time. In today’s blog post we will see how we can drop multiple tables with a 1 drop statement.
Here is a SQL in the Sixty Seconds video where I explain how you can drop multiple tables with a single DROP statement.
Commonly when we have to drop more than one table, we usually write a statement like the following.
DROP TABLE testing1; DROP TABLE testing2; DROP TABLE testing3;
However, there is no need to repeat the words DROP TABLE, you can write a single statement like the following:
DROP TABLE testing1,testing2,testing3;
If you are not sure if the table exists or not, you can also check that with IF EXISTS keywords.
DROP TABLE IF EXISTS testing1,testing2,testing3;
Additionally, do not forget that this 1 drop statement that drops multiple tables also supports transactions just like any other statement.
If you like my videos, I request you to like, share, comment, and subscribe to these videos. Here is the link to my YouTube Videos.
Reference: Pinal Dave (https://blog.sqlauthority.com)
