Listing 4: The sp_display_curr_tsql_in_sessions stored procedure

USE master
GO
CREATE PROCEDURE sp_display_curr_tsql_in_sessions 
AS
  BEGIN 
    SET NOCOUNT ON
    DECLARE @currSID int
    DECLARE @tsqlText varchar (max)
    DECLARE @sqlHndl varbinary(20)
    DECLARE c_spids CURSOR FOR SELECT spid
      FROM sys.sysprocesses
    CREATE TABLE #currSQLinfo (spid int, sqltext text)
    OPEN c_spids
    FETCH c_spids into @currSID
    WHILE @@fetch_status = 0
    BEGIN
      SELECT @sqlHndl = sql_handle
         FROM sys.sysprocesses
         WHERE spid = @currSID
        SELECT @tsqlText = convert (varchar(max),[text])
        FROM ::fn_get_sql (@sqlHndl)
        IF ISNULL (@tsqlText,'*') != '*'
        BEGIN 
        INSERT #currSQLinfo VALUES (@currSID,@tsqlText)
        END 
        FETCH c_spids INTO @currSID
    END 
    CLOSE c_spids
    DEALLOCATE c_spids
    SELECT * FROM #currSQLinfo
    DROP TABLE #currSQLinfo
    SET NOCOUNT OFF
  END
GO