SQL Server: Drop All Tables

Fast way to drop all tables in a database:

SELECT name INTO #tables from sys.objects where type = 'U'
while (SELECT count(1) FROM #tables) > 0
@sql varchar(max)
declare @tbl varchar(255)
SELECT top 1 @tbl = name FROM #tables
SET @sql = 'drop table [' + @tbl + ']'
DELETE FROM #tables where name = @tbl
TABLE #tables;

This is the latest tweaked code, but doesn't take foreign keys into account. You can (usually) run it multiple times, sweeping up more and more (despite foreign keys) until there's nothing left to drop. Still looking for a smarter version that's foreign key savvy.

The (undocumented?) sp_MSForEachTable stored procedure is handy, and more flexible in general. For the lazy (myself included):

USE [foo]
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"
EXEC sp_MSforeachtable @command1 = "PRINT '?'"

That'll whine about tables it can't drop due to foreign keys, so just run as often as needed until nothing's left.

Skip to main content