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?
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
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.