SQL Server, Drop All Tables in Database Quickly
Posted in: SQL | April 22nd, 2008
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').
SQL:
-
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;