Kill all connection to database

use master
go

if exists(select * from master.dbo.sysobjects where name = 'proc_kill_dbconnections' and type = 'P')
    drop procedure dbo.proc_kill_dbconnections
go

create procedure proc_kill_dbconnections
/*****************************************************************************************************
*
* Author Rafal Skotak
* Purpose Procedure tries to kill all connections to the specified database
*
* Date 2011.02.18
*
******************************************************************************************************/
    @dbname    sysname
with encryption
as
begin
    set nocount on

    ------- prepare temporary tables
    -------------------------------------------
    create table #temp_processes_table
    (
        spid             int primary key, 
        ecid             int,
        status             sysname, 
        loginame         sysname null, 
        hostname         sysname null, 
        blk             int, 
        dbname             sysname null, 
        cmd             sysname null,
        request_id        int null,
        mod             int not null default 0
    );

    -- get processes list
    --------------------------
    insert into #temp_processes_table (spid, ecid, status, loginame, hostname, blk, dbname, cmd, request_id) exec sp_who

    declare @count        int
    declare @process_id    int
    declare @cmd        varchar(8000)

    select @count = count(*) from #temp_processes_table where mod = 0

    while @count > 0
    begin
        set @process_id = NULL

        select top 1 @process_id = spid from #temp_processes_table where mod = 0 and dbname = @dbname

        if @process_id is NULL
            break

        print @process_id

        set @cmd = 'kill ' + cast(@process_id as varchar)

        exec (@cmd)

        update #temp_processes_table set mod = 1 where spid = @process_id

        select @count = count(*) from #temp_processes_table where mod = 0 and dbname = @dbname
    end

    -- cleanup - drop temporary tables
    ---------------------------------------
    drop table #temp_processes_table
end
go

-- example

-- exec dbo.proc_kill_dbconnections 'Northwind'