Kill All SPIDs for given Databases

So as promised in my last post, I'm going to include a script that will loop over a list of databases and kill all SPIDS connected to them. If you want to use this, you'll need to install the SP_WHO3 proc and the StringToTable function that I posted in the past two posts.

DECLARE @DBsToKill VARCHAR(MAX) = 'DB1,DB2,DB3' --List of DBs here, comma delimited
  ,@KillTest VARCHAR(20) = 'TEST' --TEST=print SPIDs; KILL=Kill SPIDs
  ,@SQL VARCHAR(MAX)

SET @SQL = '
SET NOCOUNT ON
CREATE TABLE #SpidsToKill (
 SPID VARCHAR(5)
 ,[Status] VARCHAR(max)
 ,[login] VARCHAR(max)
 ,[HostName] VARCHAR(max)
 ,BlkBy VARCHAR(max)
 ,DBName VARCHAR(100)
 ,Command VARCHAR(max)
 ,CPUTime VARCHAR(max)
 ,DiskIO VARCHAR(max)
 ,LastBatch VARCHAR(MAX)
 ,ProgramName VARCHAR(MAX)
 ,SPID2 VARCHAR(max)
 ,RequestID VARCHAR(max)
)
'
DECLARE @DBName VARCHAR(100)

DECLARE Z_CURSOR CURSOR FOR
 SELECT TableValue
 FROM dbo.StringToTable(@DBsToKill,',')
 
OPEN Z_CURSOR
FETCH NEXT FROM Z_CURSOR 
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
 
 SET @SQL += '
 INSERT INTO #SpidsToKill

        ( SPID
        ,Status
        ,login
        ,HostName
        ,BlkBy
        ,DBName
        ,Command
        ,CPUTime
        ,DiskIO
        ,LastBatch
        ,ProgramName
        ,SPID2
        ,RequestID
        )
EXEC sp_who3 ''' + @DBName + '''
'

 FETCH NEXT FROM Z_CURSOR 
 INTO @DBName
END
CLOSE Z_CURSOR 
DEALLOCATE Z_CURSOR

IF (@KillTest = 'Kill')
BEGIN
 SET @SQL += '
 DECLARE @SPID VARCHAR(4)
  ,@InternalSQL VARCHAR(MAX) = ''''

 DECLARE Z_CURSOR CURSOR FOR
  SELECT SPID 
  FROM #SpidsToKill
  
 OPEN Z_CURSOR
 FETCH NEXT FROM Z_CURSOR 
 INTO @SPID
 WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @InternalSQL += ''
KILL '' + @SPID

  FETCH NEXT FROM Z_CURSOR 
  INTO @SPID
 END
 CLOSE Z_CURSOR 
 DEALLOCATE Z_CURSOR

 exec(@InternalSQL)
 --PRINT @InternalSQL
 '
END
ELSE
BEGIN
 SET @SQL += 'SELECT * FROM #SpidsToKill ORDER BY DBName, SPID'

END 
SET @SQL += '
DROP TABLE #SpidsToKill'

--PRINT @SQL

EXEC(@SQL)