DROP DATABASE -> database in use error

when I do DROP DATABASE I'm getting errors because it's in use. But if I do a delete from the mgmt studio gui I have the option to close all connections as part of the delete. Is there a way to achieve the same effect from a sql script?

Here is an article I wrote about that:

http://drsql.spaces.msn.com/blog/cns!80677FB08B3162E4!950.entry

Basically:

alter database <databaseName>

set single_user with rollback immediate

I find this indispensible when testing database creation scripts with a few windows open and I need to drop the database, recreate it with scripts, fix some stupid bug and then re-run things.

Two tricks:

First, most every dialog in SSMS and SSMSE (express edition) have a script button. So most things you can just get the script. On my express edition however, the alter database stuff wasn't scripted, so I cannot say with 100% accuracy (it might just be a bug :)

Second, a trick to find these things sorts of things out. If you aren't using Express Edition, you can run profiler and usually see the commands that SQL Server is running. This way, if the script doesn't work like you expect it to, you can see what they are actually running :)

|||

There are scripts that you can get to kill all users in the database... it uses a simple cursor to collect the information from the sysprocesses table or the sp_who command and run a kill command against each of the connections that releate to the database in question. If you have a look at the scripts section of www.sqlservercentral.com you will be able to find the scripts i am talking about.