I was running some database unit tests and needed to drop completely everyhing in my database. There are really many ways how to do that but I like following way because it kills all existing connections automatically and can be executed with one keystroke. It is not any rocket science but trick with bringing database offline and online again solves issue with existing connections. Then it drops and creates empty db again.

This is it:

USE master
GO
ALTER DATABASE <your_db> SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE <your_db> SET ONLINE WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE  <your_db>
GO
CREATE DATABASE <your_db>

Jakub Dvorak @ www.sqltreeo.com