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?
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.
Dig Deeper on Microsoft SQL Server Installation
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.