SQL Server: Filter output of sp_who2

Similar to [KyleMit answer][1], its possible to select directly the tables used by SP_WHO2, although I think it's only need dbo.sysprocesses table.

If someone open this SP, it can understand what it does. This is my best select to have a similar output as SP_WHO2

select convert(char(5),sp.spid) as SPID
, CASE lower(sp.status)
When 'sleeping' Then lower(sp.status)
Else upper(sp.status)
END as Status
, convert(sysname, rtrim(sp.loginame)) as LOGIN
, CASE sp.hostname
When Null Then ' .'
When ' ' Then ' .'
Else rtrim(sp.hostname)
END as HostName
, CASE isnull(convert(char(5),sp.blocked),'0')
When '0' Then ' .'
Else isnull(convert(char(5),sp.blocked),'0')
END as BlkBy
, case when sp.dbid = 0 then null when sp.dbid <> 0 then db_name(sp.dbid) end as DBName
, sp.cmd as Command
, sp.cpu as CPUTime
, sp.physical_io as DiskIO
, sp.last_batch as LastBatch
, sp.program_name as ProgramName
from master.dbo.sysprocesses sp (nolock)
;

Over this select, you can select the fields you need and have the order you want.


[1]:

[To see links please register here]

Slight improvement to [Astander's answer][1]. I like to put my criteria at top, and make it easier to reuse day to day:

DECLARE @Spid INT, @Status VARCHAR(MAX), @Login VARCHAR(MAX), @HostName VARCHAR(MAX), @BlkBy VARCHAR(MAX), @DBName VARCHAR(MAX), @Command VARCHAR(MAX), @CPUTime INT, @DiskIO INT, @LastBatch VARCHAR(MAX), @ProgramName VARCHAR(MAX), @SPID_1 INT, @REQUESTID INT

--SET @SPID = 10
--SET @Status = 'BACKGROUND'
--SET @LOGIN = 'sa'
--SET @HostName = 'MSSQL-1'
--SET @BlkBy = 0
--SET @DBName = 'master'
--SET @Command = 'SELECT INTO'
--SET @CPUTime = 1000
--SET @DiskIO = 1000
--SET @LastBatch = '10/24 10:00:00'
--SET @ProgramName = 'Microsoft SQL Server Management Studio - Query'
--SET @SPID_1 = 10
--SET @REQUESTID = 0

SET NOCOUNT ON
DECLARE @Table TABLE(
SPID INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT
)
INSERT INTO @Table EXEC sp_who2
SET NOCOUNT OFF
SELECT *
FROM @Table
WHERE
(@Spid IS NULL OR SPID = @Spid)
AND (@Status IS NULL OR Status = @Status)
AND (@Login IS NULL OR Login = @Login)
AND (@HostName IS NULL OR HostName = @HostName)
AND (@BlkBy IS NULL OR BlkBy = @BlkBy)
AND (@DBName IS NULL OR DBName = @DBName)
AND (@Command IS NULL OR Command = @Command)
AND (@CPUTime IS NULL OR CPUTime >= @CPUTime)
AND (@DiskIO IS NULL OR DiskIO >= @DiskIO)
AND (@LastBatch IS NULL OR LastBatch >= @LastBatch)
AND (@ProgramName IS NULL OR ProgramName = @ProgramName)
AND (@SPID_1 IS NULL OR SPID_1 = @SPID_1)
AND (@REQUESTID IS NULL OR REQUESTID = @REQUESTID)


[1]:

[To see links please register here]

I am writing here for future use of my own. It uses sp_who2 and insert into table variable instead of temp table because Temp table cannot be used twice if you do not drop it.
And shows blocked and blocker at the same line.

--blocked: waiting becaused blocked by blocker
--blocker: caused blocking
declare @sp_who2 table(
SPID int,
Status varchar(max),
Login varchar(max),
HostName varchar(max),
BlkBy varchar(max),
DBName varchar(max),
Command varchar(max),
CPUTime int,
DiskIO int,
LastBatch varchar(max),
ProgramName varchar(max),
SPID_2 int,
REQUESTID int
)
insert into @sp_who2 exec sp_who2
select w.SPID blocked_spid, w.BlkBy blocker_spid, tblocked.text blocked_text, tblocker.text blocker_text
from @sp_who2 w
inner join sys.sysprocesses pblocked on w.SPID = pblocked.spid
cross apply sys.dm_exec_sql_text(pblocked.sql_handle) tblocked
inner join sys.sysprocesses pblocker on case when w.BlkBy = ' .' then 0 else cast(w.BlkBy as int) end = pblocker.spid
cross apply sys.dm_exec_sql_text(pblocker.sql_handle) tblocker
where pblocked.Status = 'SUSPENDED'

I made an improvement in order to obtain not only the blocked processes but also the blocking process:


```jql
DECLARE @Table TABLE
(
SPID INT, Status VARCHAR(MAX), LOGIN VARCHAR(MAX), HostName VARCHAR(MAX), BlkBy VARCHAR(MAX), DBName VARCHAR(MAX), Command VARCHAR(MAX), CPUTime INT, DiskIO INT, LastBatch VARCHAR(MAX), ProgramName VARCHAR(MAX), SPID_1 INT, REQUESTID INT
)

INSERT INTO @Table EXEC sp_who2

SELECT *
FROM @Table
WHERE
BlkBy not like ' .'
or
SPID in (SELECT BlkBy from @Table where BlkBy not like ' .')

delete from @Table
```