TSQL to kill all processes of a database except your own process

To kill all the active processes of a database except your own process execute the below query after providing the database name in parameter (@DBName). It provides you the Kill statements with SPID's in result set, Now copy and paste the result set in new window and execute that statements, it will kill all processes of a database except yours.

SQL CODE:

IF OBJECT_ID('TEMPDB..#SP_WHO') IS NOT NULL
    DROP TABLE #SP_WHO;

IF OBJECT_ID('TEMPDB..#ProcessToKill') IS NOT NULL
    DROP TABLE #ProcessToKill;

DECLARE @DBName NVARCHAR(150);
SET @DBName = 'YourDatabaseName';

CREATE TABLE #SP_WHO
    (
      IDX INT IDENTITY(1, 1) ,
      SpID INT ,
      ECID INT ,
      STATUS VARCHAR(50) ,
      LOGINNAME VARCHAR(100) ,
      HOSTNAME VARCHAR(100) ,
      BLK INT ,
      DBNAME VARCHAR(150) ,
      CND VARCHAR(150) ,
      REQUEST_ID BIT
    );
INSERT  INTO #SP_WHO
        ( SpID ,
          ECID ,
          STATUS ,
          LOGINNAME ,
          HOSTNAME ,
          BLK ,
          DBNAME ,
          CND ,
          REQUEST_ID
        )
        EXEC sp_who;

CREATE TABLE #ProcessToKill
    (
      Statements VARCHAR(100)
    );


DECLARE @start INT= 1;
DECLARE @End INT;
SET @End = ( SELECT MAX(IDX)
             FROM   #SP_WHO
           );
WHILE @start <= @End
    BEGIN

        INSERT  INTO #ProcessToKill
                ( Statements
                )
                SELECT  'Kill ' + CAST(SpID AS VARCHAR(50))
                FROM    #SP_WHO
                WHERE   DBNAME = @DBName
                        AND SpID <> @@SPID
                        AND IDX = @start;

        SET @start = @start + 1;

    END;

SELECT  *

FROM    #ProcessToKill;