Script that kills all processes for a given database

I am trying to write a script that kills all processes for a given database. 
 USE MASTER SELECT SPID FROM SYSPROCESSES P, SYSDATABASES D WHERE P.dbID = D.dbID AND D.NAME = 'DB'
Once I have identified the SPIDs, how do I "loop" through the rows returned and issue the KILL SPID statement?

    Requires Free Membership to View

    Login

 

 IF OBJECT_ID('dbo.sp_killalldbusers') IS NOT NULL BEGIN DROP PROCEDURE dbo.sp_killalldbusers IF OBJECT_ID('dbo.sp_killalldbusers') IS NOT NULL PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_killalldbusers >>>' ELSE PRINT '<<< DROPPED PROCEDURE dbo.sp_killalldbusers >>>' END go create procedure sp_killalldbusers @dbName varchar(32) as declare @spid smallint declare @cmd varchar(32) create table #tmp ( spid smallint, status varchar(32), loginame varchar(32), hostname varchar(32), blk char(8), dbname varchar(32), cmd varchar(255)) declare cLogin cursor for select spid from #tmp where dbname = @dbName insert into #tmp exec sp_who open cLogin fetch cLogin into @spid while @@fetch_status = 0 begin select @cmd = 'kill ' + CONVERT(char, @spid) print @cmd execute( @cmd ) fetch cLogin into @spid end close cLogin deallocate cLogin go IF OBJECT_ID('dbo.sp_killalldbusers') IS NOT NULL PRINT '<<< CREATED PROCEDURE dbo.sp_killalldbusers >>>' ELSE PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_killalldbusers >>>' Go

 

For More Information

This was first published in November 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top