Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Ziek's SQL Home

Ezekiel is an IT professional with over ten years experience in building database applications and BI/DW 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/DW solutions, .NET applications, Business Process Improvement and Project Risk Management. He is a Microsoft Certified Professional - MCTS: Microsoft SQL Server 2005 Implementation and Maintenance.

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

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;

Drop

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


Comments

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

Loading comments...