Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- declare @dbName varchar(100)
- set @dbName ='MyDatabase'
- IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @dbName)
- BEGIN
- declare @liveConnections as Table(Id int identity(1,1), name varchar(max), spid int )
- insert into @liveConnections (Name,Spid)
- 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 = @dbName)
- if(exists(select 1 from @liveConnections))
- BEGIN
- declare @counter int=1
- while(exists(select 1 from @liveConnections where Id=@counter))
- BEGIN
- declare @kill_process nvarchar(max)
- declare @spid varchar(100)
- select @kill_process=Name, @spid=cast(spid as varchar(100)) from @liveConnections where id=@counter
- SET @kill_process = 'KILL ' + @spid
- EXEC master.dbo.sp_executesql @kill_process
- PRINT 'killed spid : '+ @spid
- set @counter =@counter +1
- END
- END
- exec sp_dboption @dbName, 'single user', 'TRUE'
- DROP DATABASE [MyDatabase]
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement