Dropping All Tables In A SQL Server Database

While doing database logical model design, I found it convenient to create a SQL script I could repeatedly execute to ensure that my design was feasible in a real database.  But since my script was essentially a good number of CREATE TABLE statements, I needed to delete all the tables from my previous run.

Internet to the rescue:

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/a512be8a-376f-4fc9-8243-78dbdbe59e55/

which indicates that you can use the very handy command:

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

to execute a DROP TABLE on each user table in the database.  If you have relational integrity constraints between tables, you may need to execute this command more than once until all the referencing tables have been deleted, at which point you can execute it again to drop the (previously) referenced tables.

BE WARNED: this will DROP ALL TABLES.  You may not want to do this.  You should not do it anywhere except in a scratch database (for example, one in which you're doing logical data model design).