Custom Version of sp_who2 called sp_who3

Below is an updated version of sp_who2 which I called sp_who3. There are separate versions for SQL 7, SQL 2000 and SQL 2005. There are actually two procedures created. sp_blocked and sp_who3. sp_blocked is used by sp_who3.

sp_who3 accepts three input parameters (while sp_who2 supports two parameters). Like sp_who2, sp_who3 supports passing a specific spid and the word active. It also supports the work blocked. Passing the work blocked will return a list of all blocking spids and all blocked spids. This will assist in tracing back a blocking tree to find the start of the blocking. Passing the active parameter will return the same data as sp_who2 with the active parameter.

When you run sp_who3 with a spid as the parameter 6 or 7 second sets will be returned. The first is the output from sp_who. The second is the output from sp_who2. The third is the input buffer (first 255 characters of the last command run). The Fourth will not always appear. When it does it will show you the FULL text of the T/SQL command that is being executed (it doesn't appear in SQL 2005 as Microsoft removed the function that returns this data, as soon as I find a work around I'll post an update). The Fifth is the information about the spid in the sysprocesses table. The sixth will be the information from the sysprocess table about all spids being blocked by this spid (if no processes are being blocked a message to this extent will be displayed). The seventh record set will be the lock information for the requested process.For SQL 2005 where is says sysprocesses it is actually pulling from sys.sysprocesses.

Each version grants execute rights to all users via the public role in the master database (last couple of lines of the script). If you do not want users to be able to execute this command remove the GRANT statement.

In SQL 2005 if users have the right to run sp_who3 because of default SQL Server 2005 security they will only be able to see information about their current process. In order for them to be able to view information about all the processes grant their login the VIEW SERVER STATE right using this command.
[code SQL 2005]
[color blue]GRANT[/color] [color blue]VIEW[/color] SERVER STATE [color blue]TO[/color] [[color blue]DOMAIN[/color]] [color blue]AS[/color] [sa]
[color blue]GO[/color]
[/code]


Below is the actual code for the sp_who3 procedures.
[code SQL 7]
[color blue]use[/color] master
[color blue]if[/color] exists([color blue]select[/color] * [color blue]from[/color] master.dbo.sysobjects [color blue]where[/color] [color blue]name[/color] = [color red]'sp_blocked'[/color])
[color blue]drop[/color] [color blue]procedure[/color] sp_blocked
[color blue]go[/color]
[color blue]create[/color] [color blue]procedure[/color] sp_blocked
@spid [color blue]int[/color]
[color blue]as[/color]
[color blue]create[/color] [color blue]table[/color] #Blocked
(spid [color blue]int[/color])
[color blue]insert[/color] [color blue]into[/color] #Blocked
(spid)
[color blue]values[/color]
(@spid)

[color blue]while[/color] @@ROWCOUNT <> 0
[color blue]BEGIN[/color]
[color blue]insert[/color] [color blue]into[/color] #Blocked
(spid)
[color blue]select[/color] spid
[color blue]from[/color] master.dbo.sysprocesses
[color blue]where[/color] blocked in ([color blue]select[/color] spid [color blue]from[/color] #Blocked)
and spid not in ([color blue]select[/color] spid [color blue]from[/color] #Blocked)
[color blue]END[/color]

[color blue]delete[/color] [color blue]from[/color] #Blocked
[color blue]where[/color] spid = @spid

[color blue]if[/color] exists([color blue]select[/color] * [color blue]from[/color] #Blocked)
[color blue]BEGIN[/color]
[color blue]select[/color] *
[color blue]from[/color] master.dbo.sysprocesses
[color blue]where[/color] spid in ([color blue]select[/color] spid [color blue]from[/color] #Blocked)
[color blue]END[/color]
[color blue]else[/color]
[color blue]BEGIN[/color]
[color blue]select[/color] [color red]'No Processes are being blocked by spid '[/color] + [color #FF00FF]convert[/color]([color blue]varchar[/color](20), @spid) + [color red]'.'[/color] [color blue]as[/color] [color red]'System Message'[/color]
[color blue]END[/color]

[color blue]drop[/color] [color blue]table[/color] #Blocked
[color blue]go[/color]
[color blue]print[/color] [color red]'sp_blocked created.'[/color]



[color blue]USE[/color] master
[color blue]print[/color] [color red]'Creating sp_who3'[/color]

[color blue]if[/color] exists ([color blue]select[/color] * [color blue]from[/color] dbo.sysobjects [color blue]where[/color] id = [color #FF00FF]object_id[/color](N[color red]'[dbo].[sp_who3]'[/color]) and OBJECTPROPERTY(id, N[color red]'IsProcedure'[/color]) = 1)
[color blue]drop[/color] [color blue]procedure[/color] [dbo].[sp_who3]
[color blue]GO[/color]

[color blue]SET[/color] [color #FF00FF]QUOTED_IDENTIFIER[/color] [color blue]ON[/color]
[color blue]GO[/color]
[color blue]SET[/color] ANSI_NULLS [color blue]ON[/color]
[color blue]GO[/color]

[color blue]CREATE[/color] [color blue]procedure[/color] sp_who3
@spid [color blue]sysname[/color] = null
[color blue]as[/color]
[color blue]SET[/color] [color #FF00FF]NOCOUNT[/color] [color blue]ON[/color]
[color blue]if[/color] @spid [color blue]is[/color] null
[color blue]BEGIN[/color]
[color blue]exec[/color] sp_who2
[color blue]END[/color]
[color blue]ELSE[/color]
[color blue]BEGIN[/color]
[color blue]if[/color] [color #FF00FF]lower[/color]([color #FF00FF]convert[/color]([color blue]varchar[/color](10), @spid)) = [color red]'active'[/color]
[color blue]BEGIN[/color]
[color blue]exec[/color] sp_who2 active
[color blue]END[/color]
[color blue]ELSE[/color]
[color blue]BEGIN[/color]
[color blue]declare[/color] @spid_i [color blue]int[/color]
[color blue]set[/color] @spid_i = @spid
[color blue]exec[/color] sp_who @spid_i
[color blue]exec[/color] sp_who2 @spid_i
[color blue]dbcc[/color] [color #FF00FF]inputbuffer[/color] (@spid_i)
[color blue]select[/color] * [color blue]from[/color] master.dbo.sysprocesses [color blue]where[/color] spid = @spid_i
[color blue]exec[/color] sp_blocked @spid_i
[color blue]exec[/color] sp_lock @spid_i
[color blue]END[/color]
[color blue]END[/color]
[color blue]GO[/color]
[color blue]grant[/color] [color blue]exec[/color] [color blue]on[/color] sp_who3 [color blue]to[/color] [color blue]public[/color]
[color blue]go[/color][/code]

[code SQL 2000]
[color blue]use[/color] master
[color blue]if[/color] exists([color blue]select[/color] * [color blue]from[/color] master.dbo.sysobjects [color blue]where[/color] [color blue]name[/color] = [color red]'sp_blocked'[/color])
[color blue]drop[/color] [color blue]procedure[/color] sp_blocked
[color blue]go[/color]
[color blue]create[/color] [color blue]procedure[/color] dbo.sp_blocked
@spid [color blue]int[/color]
[color blue]as[/color]
[color blue]create[/color] [color blue]table[/color] #Blocked
(spid [color blue]int[/color])
[color blue]insert[/color] [color blue]into[/color] #Blocked
(spid)
[color blue]values[/color]
(@spid)

[color blue]while[/color] @@ROWCOUNT <> 0
[color blue]BEGIN[/color]
[color blue]insert[/color] [color blue]into[/color] #Blocked
(spid)
[color blue]select[/color] spid
[color blue]from[/color] master.dbo.sysprocesses
[color blue]where[/color] blocked in ([color blue]select[/color] spid [color blue]from[/color] #Blocked)
and spid not in ([color blue]select[/color] spid [color blue]from[/color] #Blocked)
[color blue]END[/color]

[color blue]delete[/color] [color blue]from[/color] #Blocked
[color blue]where[/color] spid = @spid

[color blue]delete[/color] [color blue]from[/color] #Blocked
[color blue]where[/color] spid [color blue]is[/color] null

[color blue]if[/color] exists([color blue]select[/color] * [color blue]from[/color] #Blocked)
[color blue]BEGIN[/color]
[color blue]select[/color] *
[color blue]from[/color] master.dbo.sysprocesses
[color blue]where[/color] spid in ([color blue]select[/color] spid [color blue]from[/color] #blocked)
[color blue]END[/color]
[color blue]else[/color]
[color blue]BEGIN[/color]
[color blue]select[/color] [color red]'No Processes are being blocked by spid '[/color] + [color #FF00FF]convert[/color]([color blue]varchar[/color](20), @spid) + [color red]'.'[/color] [color blue]as[/color] [color red]'System Message'[/color]
[color blue]END[/color]

[color blue]drop[/color] [color blue]table[/color] #Blocked
[color blue]go[/color]
[color blue]print[/color] [color red]'sp_blocked created.'[/color]

[color blue]USE[/color] master
[color blue]print[/color] [color red]'Creating sp_who3'[/color]

[color blue]if[/color] exists ([color blue]select[/color] * [color blue]from[/color] dbo.sysobjects [color blue]where[/color] [color blue]name[/color] = [color red]'sp_who3'[/color])
[color blue]drop[/color] [color blue]procedure[/color] [dbo].[sp_who3]
[color blue]GO[/color]
[color blue]SET[/color] [color #FF00FF]QUOTED_IDENTIFIER[/color] [color blue]ON[/color]
[color blue]GO[/color]
[color blue]SET[/color] ANSI_NULLS [color blue]ON[/color]
[color blue]GO[/color]

[color blue]CREATE[/color] [color blue]procedure[/color] sp_who3
@spid [color blue]sysname[/color] = null
[color blue]as[/color]
[color blue]DECLARE[/color] @spid_i [color blue]INT[/color]
[color blue]DECLARE[/color] @spid_only [color blue]bit[/color]
[color blue]SET[/color] [color #FF00FF]NOCOUNT[/color] [color blue]ON[/color]
[color blue]if[/color] @spid [color blue]is[/color] null
[color blue]BEGIN[/color]
[color blue]exec[/color] sp_who2
[color blue]END[/color]
[color blue]ELSE[/color]
[color blue]BEGIN[/color]
[color blue]set[/color] @spid_only = 1
[color blue]if[/color] [color #FF00FF]lower[/color]([color #FF00FF]cast[/color](@spid [color blue]as[/color] [color blue]varchar[/color](10))) = [color red]'active'[/color]
[color blue]BEGIN[/color]
[color blue]set[/color] @spid_only = 0
[color blue]exec[/color] sp_who2 [color red]'active'[/color]
[color blue]END[/color]
[color blue]if[/color] [color #FF00FF]lower[/color]([color #FF00FF]cast[/color](@spid [color blue]as[/color] [color blue]varchar[/color](10))) = [color red]'blocked'[/color]
[color blue]BEGIN[/color]
[color blue]DECLARE[/color] @blocked [color blue]TABLE[/color]
(spid [color blue]int[/color],
blocked [color blue]int[/color])

[color blue]INSERT[/color] [color blue]INTO[/color] @blocked
[color blue]select[/color] spid, blocked
[color blue]from[/color] sysprocesses
[color blue]where[/color] blocked <> 0

[color blue]insert[/color] [color blue]into[/color] @blocked
[color blue]select[/color] spid, blocked
[color blue]from[/color] sysprocesses
[color blue]where[/color] spid in ([color blue]select[/color] blocked [color blue]from[/color] @blocked)

[color blue]set[/color] @spid_only = 0
[color blue]select[/color] sysprocesses.spid [color blue]as[/color] [color red]'SPID'[/color],
sysprocesses.status,
sysprocesses.loginame [color blue]as[/color] [color red]'Login'[/color],
sysprocesses.hostname [color blue]as[/color] [color red]'HostName'[/color],
sysprocesses.blocked [color blue]as[/color] [color red]'BlkBy'[/color],
sysdatabases.name [color blue]as[/color] [color red]'DBName'[/color],
sysprocesses.cmd [color blue]as[/color] [color red]'Command'[/color],
sysprocesses.cpu [color blue]as[/color] [color red]'CPUTime'[/color],
sysprocesses.physical_io [color blue]as[/color] [color red]'DiskIO'[/color],
sysprocesses.last_batch [color blue]as[/color] [color red]'LastBatch'[/color],
sysprocesses.program_name [color blue]as[/color] [color red]'ProgramName'[/color],
sysprocesses.spid [color blue]as[/color] [color red]'SPID'[/color]
[color blue]from[/color] sysprocesses
[color #FF00FF]left[/color] [color blue]outer[/color] [color blue]join[/color] sysdatabases [color blue]on[/color] sysprocesses.dbid = sysdatabases.dbid
[color blue]where[/color] spid in ([color blue]select[/color] spid [color blue]from[/color] @blocked)
[color blue]END[/color]

[color blue]if[/color] @spid_only = 1
[color blue]BEGIN[/color]
[color blue]set[/color] @spid_i = @spid
[color blue]exec[/color] sp_who @spid_i
[color blue]exec[/color] sp_who2 @spid_i
[color blue]dbcc[/color] [color #FF00FF]inputbuffer[/color] (@spid_i)
[color green]/*Start Get Output Buffer*/[/color]
[color blue]if[/color] exists ([color blue]select[/color] * [color blue]from[/color] sysobjects [color blue]where[/color] [color blue]name[/color] = [color red]'fn_get_sql'[/color])
[color blue]BEGIN[/color]
[color blue]declare[/color] @SQL_Handle [color blue]binary[/color](20)
[color blue]select[/color] @SQL_Handle = sql_handle
[color blue]from[/color] master.dbo.sysprocesses
[color blue]where[/color] spid = @spid_i

[color blue]if[/color] @SQL_Handle [color blue]is[/color] not null
[color blue]BEGIN[/color]
[color blue]Create[/color] [color blue]Table[/color] #SQL
(SQLText [color blue]text[/color])
[color blue]insert[/color] [color blue]into[/color] #SQL
[color blue]SELECT[/color] [[color blue]text[/color]]
[color blue]FROM[/color] ::fn_get_sql(@SQL_Handle)
[color blue]if[/color] exists([color blue]select[/color] * [color blue]from[/color] #SQL)
[color blue]select[/color] SQLText [color red]'Full SQL Query'[/color] [color blue]from[/color] #SQL
[color blue]drop[/color] [color blue]table[/color] #SQL
[color blue]END[/color]
[color blue]END[/color]
[color green]/*End Get Output Buffer*/[/color]
[color blue]select[/color] * [color blue]from[/color] master.dbo.sysprocesses [color blue]where[/color] spid = @spid_i
[color blue]exec[/color] sp_blocked @spid_i
[color blue]exec[/color] sp_lock @spid_i
[color blue]END[/color]
[color blue]END[/color]
[color blue]GO[/color]
[color blue]print[/color] [color red]'sp_who3 created.'[/color]
[color blue]go[/color]
[color blue]GRANT[/color] [color blue]exec[/color] [color blue]on[/color] sp_who3 [color blue]to[/color] [color blue]public[/color]
[color blue]GO[/color][/code]

[code SQL 2005]
[color blue]use[/color] master
[color blue]if[/color] exists([color blue]select[/color] * [color blue]from[/color] master.[color green]sys.objects[/color] [color blue]where[/color] [color blue]name[/color] = [color red]'sp_blocked'[/color])
[color blue]drop[/color] [color blue]procedure[/color] sp_blocked
[color blue]go[/color]
[color blue]create[/color] [color blue]procedure[/color] dbo.sp_blocked
@spid [color blue]int[/color]
[color blue]as[/color]
[color blue]create[/color] [color blue]table[/color] #Blocked
(spid [color blue]int[/color])
[color blue]insert[/color] [color blue]into[/color] #Blocked
(spid)
[color blue]values[/color]
(@spid)

[color blue]while[/color] @@ROWCOUNT <> 0
[color blue]BEGIN[/color]
[color blue]insert[/color] [color blue]into[/color] #Blocked
(spid)
[color blue]select[/color] spid
[color blue]from[/color] master.[color green]sys.sysprocesses[/color]
[color blue]where[/color] blocked in ([color blue]select[/color] spid [color blue]from[/color] #Blocked)
and spid not in ([color blue]select[/color] spid [color blue]from[/color] #Blocked)
[color blue]END[/color]

[color blue]delete[/color] [color blue]from[/color] #Blocked
[color blue]where[/color] spid = @spid

[color blue]delete[/color] [color blue]from[/color] #Blocked
[color blue]where[/color] spid [color blue]is[/color] null

[color blue]if[/color] exists([color blue]select[/color] * [color blue]from[/color] #Blocked)
[color blue]BEGIN[/color]
[color blue]select[/color] *
[color blue]from[/color] master.[color green]sys.sysprocesses[/color]
[color blue]where[/color] spid in ([color blue]select[/color] spid [color blue]from[/color] #blocked)
[color blue]END[/color]
[color blue]else[/color]
[color blue]BEGIN[/color]
[color blue]select[/color] [color red]'No Processes are being blocked by spid '[/color] + [color #FF00FF]convert[/color]([color blue]varchar[/color](20), @spid) + [color red]'.'[/color] [color blue]as[/color] [color red]'System Message'[/color]
[color blue]END[/color]

[color blue]drop[/color] [color blue]table[/color] #Blocked
[color blue]go[/color]
[color blue]print[/color] [color red]'sp_blocked created.'[/color]

[color blue]USE[/color] master
[color blue]print[/color] [color red]'Creating sp_who3'[/color]

[color blue]if[/color] exists ([color blue]select[/color] * [color blue]from[/color] [color green]sys.objects[/color] [color blue]where[/color] [color blue]name[/color] = [color red]'sp_who3'[/color])
[color blue]drop[/color] [color blue]procedure[/color] [dbo].[sp_who3]
[color blue]GO[/color]
[color blue]SET[/color] [color #FF00FF]QUOTED_IDENTIFIER[/color] [color blue]ON[/color]
[color blue]GO[/color]
[color blue]SET[/color] ANSI_NULLS [color blue]ON[/color]
[color blue]GO[/color]

CREATE procedure sp_who3
@spid sysname = null
as

/*
Date Creator Action
2007.02.15 mrdenny Birth
2007.05.18 mrdenny Correct Full Query Text
2007.10.08 mrdenny Added Waiting Statement to Full Query RecordSet
*/

DECLARE @spid_i INT
DECLARE @spid_only bit
SET NOCOUNT ON
if @spid is null
BEGIN
exec sp_who2
END
ELSE
BEGIN
set @spid_only = 1
if lower(cast(@spid as varchar(10))) = 'active'
BEGIN
set @spid_only = 0
exec sp_who2 'active'
END
if lower(cast(@spid as varchar(10))) = 'blocked' or (isnumeric(@spid) = 1 and @spid < 0)
BEGIN
DECLARE @blocked TABLE
(spid int,
blocked int)

INSERT INTO @blocked
select spid, blocked
from sys.sysprocesses
where blocked <> 0

insert into @blocked
select spid, blocked
from sys.sysprocesses
where spid in (select blocked from @blocked)

set @spid_only = 0
select sys.sysprocesses.spid as 'SPID',
sys.sysprocesses.status,
sys.sysprocesses.loginame as 'Login',
sys.sysprocesses.hostname as 'HostName',
sys.sysprocesses.blocked as 'BlkBy',
sys.databases.name as 'DBName',
sys.sysprocesses.cmd as 'Command',
sys.sysprocesses.cpu as 'CPUTime',
sys.sysprocesses.physical_io as 'DiskIO',
sys.sysprocesses.last_batch as 'LastBatch',
sys.sysprocesses.program_name as 'ProgramName',
sys.sysprocesses.spid as 'SPID'
from sys.sysprocesses
left outer join sys.databases on sys.sysprocesses.dbid = sys.databases.database_id
where spid in (select spid from @blocked)
END

if @spid_only = 1
BEGIN
DECLARE @sql_handle varbinary(64)
DECLARE @stmt_start int
DECLARE @stmt_end int

set @spid_i = @spid

SELECT @sql_handle = sql_handle,
@stmt_start = stmt_start,
@stmt_end = stmt_end
from sys.sysprocesses
where spid = @spid_i

exec sp_who @spid_i
exec sp_who2 @spid_i
dbcc inputbuffer (@spid_i)
/*Start Get Output Buffer*/
select text as 'Full Query',
case when @stmt_start < 0 then
substring(text, @stmt_start/2, (@stmt_end/2)-(@stmt_start/2))
else
null
end as 'Current Command'
from sys.dm_exec_sql_text(@sql_handle)
/*End Get Output Buffer*/
select * from master.sys.sysprocesses where spid = @spid_i
exec sp_blocked @spid_i
exec sp_lock @spid_i
END
END[/code]
[img http://www.mrdenny.com/marker/sp_who3.jpg]