Cacher is the code snippet organizer for pro developers

--exec sp_who2  

-- kill a single db connection
 USE master;  
 GO  
 ALTER   DATABASE Leelanau_JMS  
 SET   SINGLE_USER  
 WITH    ROLLBACK    IMMEDIATE   ;  
 GO  
 ALTER   DATABASE Leelanau_JMS  
 SET   MULTI_USER;  
 GO 


-- all connections
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  
    @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'