When Doing database Restore sometimes we may need to kill the spid’s in the database so that the connections are not open. Going through the spids in sp_who or sp_who2 can sometimes be a tedious task if there are more connections to the database. The following TSQL should generate the list of kill statements for a particular database with the spids.
DECLARE @spid VARCHAR(3000)
DECLARE @Databasename VARCHAR(100)
SET @spid = ”
SET @Databasename =’AdventureWorks’
SELECT @spid = + @spid + ‘Kill ‘+ CONVERT(VARCHAR(10),spid) +’;’ FROM MASTER..sysprocesses WHERE DB_NAME(dbid) = @Databasename
PRINT @spid
