--------------------------------------------------------------------

Stored Procedure: sp_ListConnections

--------------------------------------------------------------------


USE master
GO
CREATE PROCEDURE dbo.sp_ListConnections
    @DBUltra bit = 0,
    @PCUltra bit = 0,
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @DBTrain    char(1)    = NULL,
    @PCTrain varchar(2000) = NULL
AS

SET NOCOUNT ON

DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int

SET @Status = 0

DECLARE @Task varchar(4000)

DECLARE @Work varchar(2000)

DECLARE @Host varchar(200)

DECLARE @SPID smallint

DECLARE @SPOT smallint

SET @SPOT = CASE WHEN CHARINDEX('2000 - 8',@@VERSION,1) > 0 THEN 50 ELSE 12 END

SET @Work = REPLACE(@PCTrain,CHAR(94),CHAR(39))

IF @PCTrain IS NULL

    BEGIN

       SELECT P.spid AS [ID]
            , RTRIM(P.hostname) AS [Client]
            , RTRIM(P.loginame) AS [Login]
            , RTRIM(P.program_name) AS [Application]
            , RTRIM(O.name) AS [Database]
            , P.open_tran AS [Tran]
            , P.blocked AS [Block]
            , STR(P.waittime/1000.0,5) AS [Wait]
            , CASE P.waittype
              WHEN 0x0000 THEN ''
              WHEN 0x0001 THEN 'Lock: Schema S'
              WHEN 0x0002 THEN 'Lock: Schema M'
              WHEN 0x0003 THEN 'Lock: S'
              WHEN 0x0004 THEN 'Lock: U'
              WHEN 0x0005 THEN 'Lock: X'
              WHEN 0x0006 THEN 'Lock: IS'
              WHEN 0x0007 THEN 'Lock: IU'
              WHEN 0x0008 THEN 'Lock: IX'
              WHEN 0x0009 THEN 'Lock: SIU'
              WHEN 0x000A THEN 'Lock: SIX'
              WHEN 0x000B THEN 'Lock: UIX'
              WHEN 0x000C THEN 'Lock: BU'
              WHEN 0x000D THEN 'Lock: RangeS S'
              WHEN 0x000E THEN 'Lock: RangeS U'
              WHEN 0x000F THEN 'Lock: RangeI N'
              WHEN 0x0010 THEN 'Lock: RangeI S'
              WHEN 0x0011 THEN 'Lock: RangeI U'
              WHEN 0x0012 THEN 'Lock: RangeI X'
              WHEN 0x0013 THEN 'Lock: RangeX S'
              WHEN 0x0014 THEN 'Lock: RangeX U'
              WHEN 0x0015 THEN 'Lock: RangeX X'
              WHEN 0x0041 THEN 'DTC'
              WHEN 0x0042 THEN 'OLEDB Provider'
              WHEN 0x0081 THEN 'Writelog'
              WHEN 0x0208 THEN 'CX Packet List'
              WHEN 0x020A THEN 'Shutdown'
              WHEN 0x020B THEN 'WAITFOR'
              WHEN 0x0400 THEN 'Latch NL'
              WHEN 0x0401 THEN 'Latch KP'
              WHEN 0x0402 THEN 'Latch SH'
              WHEN 0x0403 THEN 'Latch UP'
              WHEN 0x0404 THEN 'Latch EX'
              WHEN 0x0405 THEN 'Latch DT'
              WHEN 0x0410 THEN 'PageLatch NL'
              WHEN 0x0411 THEN 'PageLatch KP'
              WHEN 0x0412 THEN 'PageLatch SH'
              WHEN 0x0413 THEN 'PageLatch UP'
              WHEN 0x0414 THEN 'PageLatch EX'
              WHEN 0x0415 THEN 'PageLatch DT'
              WHEN 0x0420 THEN 'PageIOLatch NL'
              WHEN 0x0421 THEN 'PageIOLatch KP'
              WHEN 0x0422 THEN 'PageIOLatch SH'
              WHEN 0x0423 THEN 'PageIOLatch UP'
              WHEN 0x0424 THEN 'PageIOLatch EX'
              WHEN 0x0425 THEN 'PageIOLatch DT'
              WHEN 0x0800 THEN 'Network IO'
                          ELSE 'System Task' END AS [Type]
            ,   RTRIM(P.status) AS [Status]
            ,    LEFT(P.cmd,08) AS [Command]
            , CONVERT(varchar(20),P.last_batch,20) AS [Submission]
            , CONVERT(varchar(20),P.login_time,20) AS [Connection]
         FROM master.dbo.sysprocesses AS P
         JOIN master.dbo.sysdatabases AS O
           ON P.dbid = O.dbid
    LEFT JOIN master.dbo.sysprocesses AS Z
           ON P.spid = Z.blocked
        WHERE P.spid > @SPOT
          AND (@DBIntra IS NULL OR CHARINDEX('|'+CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END+'|','|'+(@DBIntra)+'|') > 0)
          AND (@DBExtra IS NULL OR CHARINDEX('|'+CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END+'|','|'+(@DBExtra)+'|') = 0)
          AND (@PCIntra IS NULL OR               CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END     LIKE @PCIntra)
          AND (@PCExtra IS NULL OR               CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END NOT LIKE @PCExtra)
          AND CASE
              WHEN @DBUltra = 0
              THEN 1
              WHEN P.blocked != 0
              THEN 1
              WHEN Z.spid IS NOT NULL
              THEN 1
              ELSE 0 END > 0
          AND CASE
              WHEN @PCUltra = 0
              THEN 1
              WHEN P.spid != @@PROCID AND RTRIM(P.status) != 'sleeping'
              THEN 1
              WHEN P.spid != @@PROCID AND  LEFT(P.cmd,08) != 'AWAITING'
              THEN 1
              ELSE 0 END > 0
     ORDER BY [Client],[Login],[Application],[Database],[Connection],[Submission]

    SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

    END

ELSE

    BEGIN

    IF ISNUMERIC(@PCTrain) <> 0

        BEGIN

        SELECT @Work = description FROM master.dbo.sysmessages WHERE error = CONVERT(int,@PCTrain)

        SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

        END

    IF CHARINDEX('@@SPID',@Work) = 0

        BEGIN

          DECLARE Items CURSOR FAST_FORWARD FOR
           SELECT RTRIM(P.hostname)
             FROM master.dbo.sysprocesses AS P
             JOIN master.dbo.sysdatabases AS O
               ON P.dbid = O.dbid
        LEFT JOIN master.dbo.sysprocesses AS Z
               ON P.spid = Z.blocked
            WHERE P.spid > @SPOT
              AND (@DBIntra IS NULL OR CHARINDEX('|'+CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END+'|','|'+(@DBIntra)+'|') > 0)
              AND (@DBExtra IS NULL OR CHARINDEX('|'+CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END+'|','|'+(@DBExtra)+'|') = 0)
              AND (@PCIntra IS NULL OR               CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END     LIKE @PCIntra)
              AND (@PCExtra IS NULL OR               CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END NOT LIKE @PCExtra)
              AND CASE
                  WHEN @DBUltra = 0
                  THEN 1
                  WHEN P.blocked != 0
                  THEN 1
                  WHEN Z.spid IS NOT NULL
                  THEN 1
                  ELSE 0 END > 0
              AND CASE
                  WHEN @PCUltra = 0
                  THEN 1
                  WHEN P.spid != @@PROCID AND RTRIM(P.status) != 'sleeping'
                  THEN 1
                  WHEN P.spid != @@PROCID AND  LEFT(P.cmd,08) != 'AWAITING'
                  THEN 1
                  ELSE 0 END > 0
         GROUP BY RTRIM(P.hostname)
         ORDER BY RTRIM(P.hostname)

        SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

        OPEN Items

        SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

        FETCH NEXT FROM Items INTO @Host

        SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

        WHILE @@FETCH_STATUS = 0 AND @Status = 0

            BEGIN

            SET @Task = 'NET SEND ' + @Host + CHAR(32) + @Work

            EXECUTE @Return = master.dbo.xp_cmdshell @Task, NO_OUTPUT

            SET @Retain = @@ERROR

            IF @Status = 0 SET @Status = @Retain
            IF @Status = 0 SET @Status = @Return

            FETCH NEXT FROM Items INTO @Host

            SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

            END

        CLOSE Items DEALLOCATE Items

        END

    ELSE

        BEGIN

          DECLARE Items CURSOR FAST_FORWARD FOR
           SELECT P.spid
             FROM master.dbo.sysprocesses AS P
             JOIN master.dbo.sysdatabases AS O
               ON P.dbid = O.dbid
        LEFT JOIN master.dbo.sysprocesses AS Z
               ON P.spid = Z.blocked
            WHERE P.spid > @SPOT
              AND (@DBIntra IS NULL OR CHARINDEX('|'+CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END+'|','|'+(@DBIntra)+'|') > 0)
              AND (@DBExtra IS NULL OR CHARINDEX('|'+CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END+'|','|'+(@DBExtra)+'|') = 0)
              AND (@PCIntra IS NULL OR               CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END     LIKE @PCIntra)
              AND (@PCExtra IS NULL OR               CASE @DBTrain WHEN 'D' THEN RTRIM(O.name) WHEN 'A' THEN RTRIM(P.program_name) WHEN 'L' THEN RTRIM(P.loginame) ELSE RTRIM(P.hostname) END NOT LIKE @PCExtra)
              AND CASE
                  WHEN @DBUltra = 0
                  THEN 1
                  WHEN P.blocked != 0
                  THEN 1
                  WHEN Z.spid IS NOT NULL
                  THEN 1
                  ELSE 0 END > 0
              AND CASE
                  WHEN @PCUltra = 0
                  THEN 1
                  WHEN P.spid != @@PROCID AND RTRIM(P.status) != 'sleeping'
                  THEN 1
                  WHEN P.spid != @@PROCID AND  LEFT(P.cmd,08) != 'AWAITING'
                  THEN 1
                  ELSE 0 END > 0
         GROUP BY P.spid
         ORDER BY P.spid

        SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

        OPEN Items

        SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

        FETCH NEXT FROM Items INTO @SPID

        SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

        WHILE @@FETCH_STATUS = 0 AND @Status = 0

            BEGIN

            SET @Task = REPLACE(@Work,'@@SPID',CONVERT(varchar(5),@SPID))

            EXECUTE (@Task)

            SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

            FETCH NEXT FROM Items INTO @SPID

            SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

            END

        CLOSE Items DEALLOCATE Items

        END

    END

IF @Status = 1 OR @Status NOT BETWEEN 0 AND 50000 RAISERROR ('Windows error [%d]',16,1,@Status)

SET NOCOUNT OFF

RETURN (@Status)

GO

--------------------------------------------------------------------