常用脚本--Kill所有连接到指定数据库上的回话

时间:2020-12-10 18:44:48
USE [master]
GO /****** Object: StoredProcedure [dbo].[Sp_KillAllProcessInDB] Script Date: 02/07/2014 12:04:29 ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_KillAllProcessInDB]
@DbName VARCHAR(100)
AS
BEGIN
IF DB_ID(@DbName) = NULL
BEGIN
PRINT 'DataBase dose not Exist';
END
ELSE
BEGIN
DECLARE @spId VARCHAR(30); DECLARE TmpCursor CURSOR FOR
SELECT 'Kill ' + CONVERT(VARCHAR, SPID) AS SPID
FROM master..SysProcesses
WHERE DB_NAME(dbID) = @DbName
AND SPID <> @@SPID
AND dbID <> 0; OPEN TmpCursor;
FETCH NEXT FROM TmpCursor
INTO @spId; WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@spId)
FETCH NEXT FROM TmpCursor
INTO @spId
END
CLOSE TmpCursor;
DEALLOCATE TmpCursor;
END
END
GO