Introduction

This is a follow up to the article "Return Query Text Along With sp_who2 Using Dynamic Management Views". I received several comments about having the ablity to do the same thing using SQL Server 2000. Many of us are still using the previous version and would find this feature useful on the systems that have yet to be upgraded.

Examining the Query

I know sp_who2 and DBCC Inputbuffer hold the key to all the information we need to know; now the question is how to combine the data and display the results.

First of all we need a temporary table to hold the results of sp_who2 I am calling #sp_who2. Next we need a temporary table to hold the results of DBCC Inputbuffer I am calling #temp. Since DBCC Inputbuffer does not return the spid, we also need a table to hold the statement from the Inputbuffer results and the spid it belongs to. I called this one #SqlStatement.

By creating a cursor I can loop through all the spids, populate the Inputbuffer #temp table for the spid, insert the spid and the Inputbuffer results in the #SqlStatement table and then join the statements back to the sp_who2 results.

CREATE TABLE #sp_who2 
( 
 SPID INT, 
 Status VARCHAR(1000) NULL, 
 Login SYSNAME NULL, 
 HostName SYSNAME NULL, 
 BlkBy SYSNAME NULL, 
 DBName SYSNAME NULL, 
 Command VARCHAR(1000) NULL, 
 CPUTime INT NULL, 
 DiskIO INT NULL, 
 LastBatch VARCHAR(1000) NULL, 
 ProgramName VARCHAR(1000) NULL, 
 SPID2 INT 
) 

Create Table #SqlStatement
(spid int,
 statement varchar(8000))
create table #temp (x varchar(100), y int, s varchar(1000), id int
identity (1,1))

INSERT #sp_who2 EXEC sp_who2 

Declare @spid varchar(10)
Declare @Statement varchar(8000)
declare @sql varchar(1000)
DECLARE SpidCursor Cursor 
   FOR Select spid from #sp_who2
OPEN SpidCursor
FETCH NEXT FROM SpidCursor
INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN

   SET @sql = 'dbcc inputbuffer (' + @spid + ')'
   insert #temp
   exec (@sql)

   Insert Into #SqlStatement 
   Select @spid, s  From #Temp where id = (Select max(id) from #Temp)
  FETCH NEXT FROM SpidCursor
  INTO @spid
END
Close SpidCursor
Deallocate SpidCursor

Select B.Statement, A.* from #sp_who2 A Left JOIN
    #SqlStatement B ON A.spid = B.spid
Drop Table #Temp
Drop Table #SqlStatement
Drop Table #sp_who2

Conclusion

There you have it. It may not be as simple or efficient as using DMV's, but it is possible. Even though it is a lot easier to do things in SQL 2005, there is usually a way to achieve the same thing using 2000.