Kill Db

KillDb.sql

#1

SELECT  d.name , convert (smallint, req_spid) As spid
      FROM master.dbo.syslockinfo l, master.dbo.spt_values v, master.dbo.spt_values x, master.dbo.spt_values u, master.dbo.sysdatabases d
      WHERE   l.rsc_type = v.number and v.type = 'LR' and l.req_status = x.number and x.type = 'LS' and l.req_mode + 1 = u.number
                  and u.type = 'L' and l.rsc_dbid = d.dbid 
                  and rsc_dbid = (SELECT TOP 1 dbid FROM master..sysdatabases WHERE name LIKE 'my_db')
                  
 SET @kill_process =  'KILL ' + @spid      
            EXEC master.dbo.sp_executesql @kill_process
                   PRINT 'killed spid : '+ @spid
                   
sp_dboption 'my_db', 'single user', 'TRUE'
GO

sp_renamedb 'my_db', 'my_db_old'
GO

DROP DATABASE my_db_old
GO
----------------------------------------------------------------------------------
#2

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'YOUR_DABASE_NAME'

DECLARE @SQL varchar(max)

SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

--SELECT @SQL 
EXEC(@SQL)