如何用T-SQL语句实现断开指定数据库中的所有链接？

[21st_centry_fox]

如题.

比如我在系统数据库MSDB下，欲断开另一个数据库PUBS所有的用户链接。
如何用SQL语句来实现？

[goregrypeck]

杀死进程！kill

[wyb0026]

use master
declare @sql varchar(8000)
declare cr cursor global for select 'kill '+rtrim(spid) from sysprocesses where dbid=db_id('pubs')

open cr

fetch next from cr into @sql
while @@fetch_status = 0
begin
exec (@sql)
fetch next from cr into @sql
end

close cr
deallocate cr

[qzxyd]

use master
declare @spid int,@str varchar(100),@dbid int,@dbname varchar(255)
set @dbname='yl2004' --Replace with you Database Name,try it
select @dbid=dbid from master.dbo.sysdatabases WHERE name = @dbname
declare cur_spid cursor local for
select spid from master.dbo.sysprocesses where dbid=@dbid
open cur_spid
fetch from cur_spid into @spid
while @@fetch_status=0
begin
set @str='kill '+Cast(@spid as varchar(10))
exec(@str)
fetch from cur_spid into @spid
end
close cur_spid
deallocate cur_spid

[zjcxc]

use master
declare @dbname sysname
set @dbname=N'要断开连接的库名'
declare hCForEach cursor global for select 'kill '+rtrim(spid) from sysprocesses where dbid=db_id(@dbname)
exec sp_msforeach_worker '?'