use master
go
declare @dbname varchar(30), @spid varchar(10), @start datetime
select @start = current_timestamp, @dbname = 'pubs'
-- Timeout after 5 mts
while(exists(Select * from sysprocesses where dbid = db_id(@dbname)) and
	datediff(mi, @start, current_timestamp) < 5)
begin
	declare spids cursor for
		select convert(varchar, spid) from sysprocesses
			where dbid = db_id(@dbname)
	open spids
	while(1=1)
	begin
		fetch spids into @spid
		if @@fetch_status < 0 break
		exec('kill ' + @spid)
	end
	deallocate spids
end
if not exists(Select * from sysprocesses where dbid = db_id(@dbname))
	exec sp_dboption @dbname, offline, true
else
begin
	print 'The following processes are still using the database:'
	select spid, cmd, status, last_batch, open_tran, program_name, hostname
	from sysprocesses
	where dbid = db_id(@dbname)
end
go