SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

How to drop all tables in a SQL Server Database whose name begins with a common value


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_”.

Ziek's SQL Home

Ezekiel is an IT professional with over ten years experience in building database applications and BI solutions. He holds a BSc Computer Science degree from KNUST, Ghana and a MSc Business IT degree from Kingston Business School, London. He has delivered solutions for the Insurance, Banking, Telecom and Housing Industries and his key expertise include developing Microsoft BI solutions using the full stack and C#, Business Process Improvement and Project Risk Management. He is a Microsoft SQL Server Certified Professional.


Leave a comment on the original post [learnsqlwithezekiel.wordpress.com, opens in a new window]

Loading comments...