This is sort of just a reference for me but others might like it as well. I often find that I need to drop all the tables from a database to start over fresh and it usually is a good amount of time in between each time I do this and I go looking for this little snippet again. So here is how to drop all the tables in an SQL Server database. You may see a few more of these snippets from time to time. Let me know if its useful to you.
/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
PRINT 'Dropped Table: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])