DECLARE @dbName VARCHAR(100)
SET @dbName = 'AdventureWorks'

DECLARE @sql VARCHAR(50)
DECLARE @spid SMALLINT
DECLARE @loginame NVARCHAR(256)
DECLARE @hostname NVARCHAR(256)

DECLARE @open_connections INT

DECLARE db_cursor CURSOR FOR
SELECT spid, loginame, hostname FROM master.sys.sysprocesses
WHERE dbid = DB_ID(@dbName)

SELECT @open_connections = count(1) FROM master.sys.sysprocesses WHERE dbid = DB_ID(@dbName)
PRINT 'Killing ' + RTRIM(@open_connections) + ' connections to database ' + @dbName

OPEN db_cursor
FETCH db_cursor INTO @spid, @loginame, @hostname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Killing process: (' + RTRIM(@spid) + ') Login: (' + RTRIM(@loginame) + ') Host: (' + RTRIM(@hostname) + ')'
SET @sql = 'KILL ' + RTRIM(@spid)
EXEC(@sql)
FETCH db_cursor INTO @spid, @loginame, @hostname
END
CLOSE db_cursor
DEALLOCATE db_cursor

SELECT @open_connections = count(1) FROM master.sys.sysprocesses WHERE dbid = DB_ID(@dbName)
IF @open_connections = 0
PRINT 'All connections closed to database ' + @dbName
ELSE
PRINT RTRIM(@open_connections) + ' connections still open ' + @dbName

• Database
• SQL Server