SQL Server查询正在执行的存储过程并停止

louyaoluan

IF (EXISTS (SELECT * FROM sys.objects WHERE name = 'PRO_QUERY_KILL_EXECUTING_PRO'))
     DROP PROC [dbo].[PRO_QUERY_KILL_EXECUTING_PRO]
GO
CREATE PROC [dbo].[PRO_QUERY_KILL_EXECUTING_PRO]
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  













declare icursor cursor for SELECT [Spid] = session_Id
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50
AND session_Id NOT IN (@@SPID)
     open icursor;
     declare @spid VARCHAR(10)
     while 1=1
         begin
             fetch next from icursor into @spid
             IF(@spid IS NOT NULL AND LEN(@spid) <> 0)
             begin
                 exec ('kill ' + @spid)
             end
             IF @@FETCH_STATUS != 0 BREAK;
         end
     close icursor
     deallocate icursor

END
GO
exec PRO_QUERY_KILL_EXECUTING_PRO
View Code　　参考：http://www.iyunv.com/database/201307/227503.html
　　http://blog.csdn.net/wsc449/article/details/7242750