Skip to content

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

December 23, 2012

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

Advertisements

From → SQL

Leave a Comment

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

%d bloggers like this: