Cursor to Kill All Process in Database

December 1, 2006 by pinaldave

When you run the script please make sure that you run it in different database then the one you want all the processes to be killed.
CREATE TABLE #TmpWho(spid INT, ecid INT, status VARCHAR(150), loginame VARCHAR(150),hostname VARCHAR(150), blk INT, dbname VARCHAR(150), cmd VARCHAR(150))INSERT INTO #TmpWhoEXEC sp_whoDECLARE @spid INT
DECLARE @tString VARCHAR(15)DECLARE @getspid CURSOR
SET @getspid =   CURSOR FOR
SELECT spidFROM #TmpWhoWHERE dbname = 'mydb'OPEN @getspidFETCH NEXT FROM @getspid INTO @spidWHILE @@FETCH_STATUS = 0BEGIN
SET @tString = 'KILL ' + CAST(@spid AS VARCHAR(5))EXEC(@tString)FETCH NEXT FROM @getspid INTO @spidEND
CLOSE @getspidDEALLOCATE @getspidDROP TABLE #TmpWhoGO
Reference : Pinal Dave (http://blog.SQLAuthority.com)