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

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