Easily filter for SQL Server connections

Easily filter for SQL Server connections
===========================:
In monitoring usage patterns on SQL Server connections, you need a tool to filter for connections of interest. Enterprise Manager has a basic tool to list connections, but it's not particularly flexible or convenient. The sp_ListConnections stored procedure makes filtering for SQL Server connections easy.

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1160202,00.html

http://media.techtarget.com/digitalguide/images/Misc/sp_ListConnections.txt

--------------------------------------------------------------------
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
--------------------------------------------------------------------