如何对exec sp_who2的结果进行选择和排序?

从网上找到了下面的两个脚本, 笔者试用过, 很好. 故记在这里.

 

方法一, 用临时表

CREATE TABLE #sp_who2
(
SPID INT,
Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
      REQUESTID INT);

INSERT INTO #sp_who2 EXEC sp_who2

SELECT      * 
FROM        #sp_who2
-- Add any filtering of the results here :
WHERE       DBName = 'WSS_Content_200'
-- Add any sorting of the results here :
ORDER BY    DBName ASC
 
DROP TABLE #sp_who2

 

方法二, 用表变量

declare @tempTable table (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
      REQUESTID INT);

INSERT INTO @tempTable 
EXEC sp_who2
select *
from @tempTable
where DBName = 'WSS_Content_200'

 

资料来源

============================

sp_who2 - filtering and sorting the results

http://www.sqlmatters.com/Articles/sp_who2%20-%20filtering%20and%20sorting%20the%20results.aspx