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
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,
) Create Table #SqlStatement
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
SET @sql = 'dbcc inputbuffer (' + @spid + ')'
Insert Into #SqlStatement
Select @spid, s From #Temp where id = (Select max(id) from #Temp) FETCH NEXT FROM SpidCursor
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
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.