Scenario

I have discovered from working on projects that sometimes you may have to make use of temporary or staging tables for things like loading, parsing and data cleansing.  Let’s assume that as per your project or company’s database naming convention, these tables are all prefix with the name/value; “temp_” and after these temporary tables have served their purpose, you may want to drop them. It is easy dropping these tables when they are very few, say about two or three in number, however how do we deal with a situation where depending on the complexity of your project, we have quite a lot of these tables?

Dropping these tables one at a time will be time-consuming and that means loss of productive man-hours. Dear reader, to overcome this I would like to share with you one of the ways all these temporary tables can be dropped at one go. Assuming all the temporary/staging tables are prefix with “temp_”, the code is as below;

The variable “@tempTables” when executed will drop all the temporary tables whose name begin with “temp_”.