Script to drop all connections to a Database

There are instances when we may have to drop all the connections to a database for example to rename a database. The script below can be used to drop all the connections to the database.

DECLARE @dbname nvarchar(128)
SET @dbname = 'DB name here' -- db to drop connections 
DECLARE @processid int 
SELECT @processid = min(spid) 
from master.dbo.sysprocesses 
where dbid = db_id(@dbname) 
WHILE @processid IS NOT NULL 
BEGIN 
EXEC ('KILL ' + @processid) 
SELECT @processid = min(spid) 
from master.dbo.sysprocesses 
where dbid = db_id(@dbname) 
END

About these ads