Sorting the results of a procedure in SQL Server 2008

Question

I am executing:

exec sp_who 
go

Is it possible to sort by a field like this?

exec sp_who order by dbname
go

How would I accomplish this?

Answer 1

If possible, the best solution would be to try and refactor the procedure into a view.

EDIT

You could also modify the procedure to stick the output into a temporary table, which you could then query.

If you are only going to sort by dbname, the the simplest solution would be to modify the procedure to always sort by dbname.

Answer 2

You can accomplish this using OPENROWSET as per this question.

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC sp_who')

SELECT * FROM #MyTempTable order by [dbname]

If for whatever reason you are unable to use OPENROWSET then you will have to create a temp table that matches the output of sp_who exactly.

e.g

Create Table #temptable
(
spid smallint,
ecid smallint,
status varchar(100),
loginame varchar(100),
hostname varchar(100),
blk smallint,
dbname varchar(100),
cmd varchar(100),
request_id smallint
)

Insert Into #temptable
Exec sp_who

Select * From #temptable order by [dbname]

Answer 3

Run:

exec sp_helptext sp_who

This will give you the SQL to use. Copy this into a new query window and add in your ORDER BY clause.