杀SQL登陆的方法 if exists (select * from sysobjects where id = object_id(N'[dbo].[killspid ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) begin drop proc killspidend go create proc killspid (@dbname varchar(20)) as begin declare @sql nvarchar(500) declare @spid int set @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')' exec (@sql) open getspid fetch next from getspid into @spid while @@fetch_status < >-1 begin exec('kill '+@spid) fetch next from getspid into @spid end close getspid deallocate getspid end go___________________________________________________________________________________
Declare @SQLStr nchar(100) declare @myspid smallint declare @mycursor cursor declare whocursor cursor for select spid from master..sysprocesses where dbid=db_id('DBNAME') set @mycursor=whocursor open @mycursor fetch next from @mycursor into @myspid while @@fetch_status=0 begin --select @myspid set @SQLStr='kill '+cast(@myspid as char(3)) execute sp_executesql @SQLStrfetch next from @mycursor into @myspid end close @mycursor deallocate whocursor Go
--假设要清除数据库abc的连接 declare @spid int declare @strsql varchar(256) select @spid=min(spid) from master.dbo.sysprocesses while @spid is not null begin set @strsql= 'kill ' + cast(@spid as varchar) print(@strsql) select @spid=min(spid) from master.dbo.sysprocesses where spid> @spid and db_name(dbid) = 'abc ' and dbid <> 0 end go
if exists (select * from sysobjects where id = object_id(N'[dbo].[killspid ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
drop proc killspidend
go
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status < >-1
begin
exec('kill '+@spid)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end
go___________________________________________________________________________________
Declare @SQLStr nchar(100)
declare @myspid smallint
declare @mycursor cursor
declare whocursor cursor for
select spid from master..sysprocesses where dbid=db_id('DBNAME')
set @mycursor=whocursor
open @mycursor
fetch next from @mycursor into @myspid
while @@fetch_status=0
begin
--select @myspid
set @SQLStr='kill '+cast(@myspid as char(3))
execute sp_executesql @SQLStrfetch next from @mycursor into @myspid
end
close @mycursor
deallocate whocursor
Go
declare @spid int
declare @strsql varchar(256)
select @spid=min(spid) from master.dbo.sysprocesses
while @spid is not null
begin
set @strsql= 'kill ' + cast(@spid as varchar)
print(@strsql)
select @spid=min(spid) from master.dbo.sysprocesses
where spid> @spid and db_name(dbid) = 'abc ' and dbid <> 0
end
go