sp_who2 ORDER BY

I need to find a way to run sp_who2, but then to have it sort by a column of my choosing.
LVL 11
SweatCoderAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE TABLE #tb1_sysprocesses (
  spid INT
 ,status sysname
 ,sid SYSNAME
 ,hostname sysname
 ,program_name sysname
 ,cmd varchar(300)
 ,cpu sysname
 ,physical_io sysname
 ,blocked sysname
 ,dbid sysname
 ,loginname sysname
 ,last_batch_char sysname )

insert INTO    #tb1_sysprocesses
exec sp_who2


select * from #tb1_sysprocesses
order by 2  -- you can choose your column name here


or  you can write another sp here

ALTER procedure sp_who3
( @orderby int )
AS

CREATE TABLE #tb1_sysprocesses (
  spid INT
 ,status sysname
 ,sid SYSNAME
 ,hostname sysname
 ,program_name sysname
 ,cmd varchar(300)
 ,cpu sysname
 ,physical_io sysname
 ,blocked sysname
 ,dbid sysname
 ,loginname sysname
 ,last_batch_char sysname )

insert INTO    #tb1_sysprocesses
exec sp_who2

declare @sql nvarchar(300)
set @sql = 'select * from #tb1_sysprocesses
    order by '+ cast(@orderby as char(1))  
exec sp_executesql @sql

drop table #tb1_sysprocesses

GO
exec sp_who3 2
0
 
pai_prasadCommented:
0
 
SweatCoderAuthor Commented:
Perfect, thanks to both of you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.