SQL Server: Filter sp_who2 results for a specific database

We all know sp_who2 command in SQL Server. It populates information on current users, sessions, logins, databases and processes. surprisingly sp_who2 is widely used and is not documented in microsoft msdn. We can see document on sp_who instead.

In our organization we have many databases and users available in a SQL Server instance. When we run sp_who2 command, a huge list will get listed in unsorted order. It is tedious to scroll up and down and read the list. I just needed to see users and process details of a specific database. So I developed below SP under master database which helped a lot.

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----------------------------------------------------------------------------------------
-- Usage :    EXEC [sp_who2_db] 'DBName'
----------------------------------------------------------------------------------------
CREATE PROC [dbo].[sp_who2_db] @dbName Varchar(100)
As
Begin

Create table #sp_who2_db_Details
(
SPID varchar(5),
[Status] varchar(100),
[Login] varchar(100),
[HostName] varchar(100),
BlkBy varchar(100),
DBName varchar(100),
Command Varchar(100),
CPUTime Varchar(100),
DiskIO varchar(100),
LastBatch Varchar(100),
ProgramName Varchar(100),
SPID2 Varchar(100),
Requestid Varchar(100)
)

INSERT INTO #sp_who2_db_Details
EXEC SP_WHO2

Declare @SQL Varchar(1000)

SET @SQL = 'select * from #sp_who2_db_Details where DBName Like ''%'+@dbName +'%'''
EXEC (@SQL)

Drop Table #sp_who2_db_Details

End

• The above SP is executable in any database. Because it is created under master database.
• We need to pass database name as parameter.
• Here I have used like condition. So that we can provide partial database name. It can be modified for exact match if required.