从网上找到了下面的两个脚本, 笔者试用过, 很好. 故记在这里.
方法一, 用临时表
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