USE [MASTER]
GO

SET NOCOUNT ON;

DECLARE @DatabaseOrServerWide CHAR(1)
DECLARE @Cursor CURSOR
DECLARE @dbid INT
DECLARE @ProcID INT
DECLARE @User VARCHAR(100)
DECLARE @DatabaseName VARCHAR(200)
DECLARE @Info VARCHAR(10)
DECLARE @ProcessDetails TABLE
    (
    ProcessID INT,
    LoginName VARCHAR(200)
    )

--Kill all server processes or just process for a specific database:
SET @DatabaseOrServerWide = 'D' -- D = Database, S = Server
                        
--If database only, set database name:
SET @DatabaseName = 'ApexERP'


--Get process details for specified database
IF @DatabaseOrServerWide = 'D'
    BEGIN
        SELECT @dbid = [dbid] FROM sys.sysdatabases WHERE [name] = @DatabaseName

        IF @dbid IS NULL
            BEGIN
                PRINT @DatabaseName + ' not found on current SQL instance.'
                GOTO EndProcess
            END

        INSERT INTO @ProcessDetails
        SELECT
            MAX([spid]),
            [loginame]
        FROM
            sys.sysprocesses
        WHERE
            [dbid] = @dbid
            AND [loginame] != ''
            AND [loginame] != 'sa'
            AND [spid] != @@SPID
        GROUP BY
            [loginame]

        IF (SELECT COUNT(0) FROM @ProcessDetails) = 0
            BEGIN
                PRINT 'No users currently connected to ' + @DatabaseName + ' excluding sa processes and this thread.'
                GOTO EndProcess
            END
    END

--Get process details for entire instance
IF @DatabaseOrServerWide = 'S'
    BEGIN

        INSERT INTO @ProcessDetails
        SELECT
            MAX([spid]),
            [loginame]
        FROM
            sys.sysprocesses
        WHERE
            [loginame] != ''
            AND [loginame] != 'sa'
            AND [spid] != @@SPID
        GROUP BY
            [loginame]

        IF (SELECT COUNT(0) FROM @ProcessDetails) = 0
            BEGIN
                PRINT 'No users currently connected to instance, excluding sa processes and this thread.'
                GOTO EndProcess
            END
    END


--Kill processes
SET @Cursor = CURSOR FOR
                        SELECT
                            ProcessID,
                            LoginName
                        FROM
                            @ProcessDetails
        
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO
                            @ProcID,
                            @User

WHILE (@@FETCH_STATUS = 0)
BEGIN

    EXEC('KILL ' + @ProcID)
    PRINT 'Process killed from login: ' + @User

    FETCH NEXT FROM @Cursor INTO
                                @ProcID,
                                @User

END

CLOSE @Cursor
DEALLOCATE @Cursor

--End information
EndProcess:

SELECT @Info = COUNT(0) FROM @ProcessDetails

PRINT ''
PRINT @Info + ' processes killed.'
PRINT ''
PRINT 'Script End'