"Drop Database" + Close Existing Connections 1

[webuser]

In Management Studio, when you delete a DB, there is a check box that optionally allows you to drop all existing connections. Is there an equivalent in T-SQL? So far, I have found that the "Drop Database" command has no parameters that can be used for this. And if not, how can I, using T-SQL, drop all connections to a DB?

Thanks in advance!!!

 

[hmckillop]

you can use sp_who2, which lists all connections onto a database, and then you can run KILL (spid), replacing (spid) with the process id you have captured from sp_who2.
This will kill the process and connection.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[webuser]

Thanks. How would this be done in an automated way, like if the script had to run as part of a build?

 

[SQLDenis]

ALTER DATABASE DataBaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE


USE MASTER
DROP DATABASE DataBaseName



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Or if you still want to use KILL, the code is here

http://sqlservercode.blogspot.com/2006/01/kill-all-connections-to-your-sql.html

But ALTER DATABASE DataBaseName
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

will make the DB single user only
then switch to master and drop the DB



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[webuser]

Perfect! I will try it now. Thanks!!!

 

[SQLDenis]

No problem

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/