Return Query Text Along With sp_who2 Using SQL 2000 - SQL Server Central

Return Query Text Along With sp_who2 Using SQL 2000 - SQL Server Central

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 intidentity (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 SpidCursorINTO @spid
WHILE @@FETCH_STATUS = 0BEGIN   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 @spidEND
Close SpidCursorDeallocate SpidCursor

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