Sorting procedure results in SQL Server 2008

Question

I carry out:

exec sp_who 
go

Can I sort a field this way?

exec sp_who order by dbname
go

How to do it?

Answer 1

You can accomplish this with OPENROWSET in accordance with 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 some reason you cannot use OPENROWSET, you will need to create a temporary table that exactly matches the output sp_who.

eg

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 2

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

EDIT

, , .

dbname, dbname.

Answer 3

Run:

exec sp_helptext sp_who

This will give you SQL to use. Copy this into a new query window and add a sentence ORDER BY.