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

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