SQL Server, Drop All Tables in Database Quickly

Just a quick and dirty way of dropping all the tables in a database, without dropping the database itself.  Useful for when your writing data migration scripts 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;

6 comments for “SQL Server, Drop All Tables in Database Quickly

  1. Eugene
    August 12, 2008 at 5:42 am

    why not just use sp_MSForEachTable with a drop statement

  2. August 12, 2008 at 6:51 pm

    sp_MSForEachTable is an undocumented stored procedure, commonly unknown – the above is a ‘do it yourself’ method, and also opens the door to other interesting things that can be done on a per-table basis.

    sp_MSForEachTable will also work

  3. Neeraj
    September 7, 2008 at 9:40 pm

    But this fails when there are foriegn key references in the tables.

  4. September 11, 2008 at 7:22 pm

    Neeraj,

    Your absolutely correct. The FK references can be put into consideration and then #tables can be sorted and worked with in a fashion that would be FK friendly.

    If interested, I could do the work up on that, and update the article.

  5. Adam Ainsworth
    January 7, 2009 at 5:55 pm

    Great routine, but it fails if the name of one of your tables is a reserved word (stupidly, I named a table ‘User’).

    Therefore, change line 7 to:

    SET @sql = ‘drop table [‘ + @tbl + ‘]’

    and you’re sorted.

    Cheers,

    Adam

  6. April 4, 2012 at 6:49 pm

    if U can’t create a temp #table
    use this

    SELECT name from sys.objects where type = ‘U’
    while
    (SELECT count(name) from sys.objects where type = ‘U’) > 0
    begin
    declare @sql NVARCHAR(4000),@tbl NVARCHAR(255)
    SELECT top 1 @tbl = name from sys.objects where type = ‘U’
    SET @sql = ‘drop table [‘+@tbl+’]’
    exec sp_executesql @sql
    end

Leave a Reply