December 17, 2012

Drop All Tables in Database whose name begins with tmp




Sometimes in your projects, you may be using staging tables for purposes like loading ,parsing and cleaning data. Suppose you name all of these table with the prefix tmp_ and after these tables have served their purpose, you now want to drop all these tables.

Here are two ways to do this:

1. Execute Drop table statements - Copy the result of this query which generates DROP TABLE statements and execute the result again. Eg:

select 'drop table '+table_name from INFORMATION_SCHEMA.TABLES where table_name like 'tmp_%'

The above query generates drop table statements like

drop table tmp_test
drop table tmp_emp
drop table tmp_data


provided there are tables named tmp_test, tmp_test and tmp_data in the database.

2. Concatenate drop table statements and execute it as a whole

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+' drop table '+table_name from INFORMATION_SCEHMA.TABLES where table_name like 'tmp_%'
exec(@sql)


The variable @sql will have drop table statements concatenated for each table. It will have the string drop table tmp_test drop table tmp_emp drop table tmp_data

When you execute the string generated from that variable, all those tables get dropped


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

 
  Feedback:

comments

0 Responses to "Drop All Tables in Database whose name begins with tmp"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions