SQL Server: Drop All Tables

  • Article

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
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;

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.