Drop All Tables in a Database Quickly (SQL Server)

Send Us a Sign! (Contact Us!)
Word PDF Epub Text
XML OpenOffice XPS MHT

Just a quick and dirty way of dropping all the [gs table]s in a database, without dropping the [gs database] itself.

[tweet]

Useful for when your writing data migration [gs script]s and staging data and need to wipe out your development environment real quick … or, when you’ve found that uber cool sql-injection-able site and want to wreak some havoc (I don’t condone the latter, but I do think its funny from time to time when it happens to the ‘big companies’).

select name into #tables from sys.objects where type = 'U'

while (select count(1) from #tables) > 0
begin
declare @sql varchar(max)
declare @tbl varchar(255)
select top 1 @tbl = name from #tables
set @sql = 'drop table ' + @tbl
exec(@sql)
delete from #tables where name = @tbl
end
drop table #tables;