What SQL is currently executing?

Have you wondered what SQL is currently executing on SQL Server, or which statement in a stored procedure is currently executing? Below is a script to create the sp_currentsql stored procedure.

I got this contribution from a colleague, thanks J.

CREATE  PROCEDURE [dbo].[sp_currentsql]
AS

SET NOCOUNT ON

CREATE TABLE #SP_WHO2 (
 SPID  SMALLINT,
 Status  NCHAR(30),
 Login  NCHAR(128),
 HostName  NCHAR(128),
 BlkBy  CHAR(5),
 DBName  NCHAR(128),
 Command  NCHAR(16),
 CPUTime  INTEGER,
 DiskIO  INTEGER,
 LastBatch  VARCHAR(20),
 ProgramName NCHAR(128),
 SPID_2 SMALLINT,
 RequestID int
)

INSERT INTO #SP_WHO2
exec sp_who2 active

DECLARE @SPID int
DECLARE @sql_handle binary(20), @handle_found bit
DECLARE @stmt_start int, @stmt_end int
DECLARE @line varchar(8000), @wait_str varchar(8)

DECLARE curSPID CURSOR FOR
 SELECT DISTINCT SPID
 FROM #SP_WHO2
 WHERE Status != 'BACKGROUND' AND HostName != '  .' AND SPID != @@spid
OPEN curSPID


FETCH NEXT FROM curSPID INTO @SPID

WHILE @@FETCH_STATUS = 0 BEGIN
 SELECT @sql_handle = sql_handle,
 @stmt_start = stmt_start/2,
 @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
 FROM master.dbo.sysprocesses
 WHERE spid = @SPID
 AND ecid = 0

 SELECT  @line=SUBSTRING( text,
    COALESCE(NULLIF(@stmt_start, 0), 1),
    CASE @stmt_end
     WHEN -1
      THEN DATALENGTH(text)
     ELSE
      (@stmt_end - @stmt_start)
     END
   )
  FROM ::fn_get_sql(@sql_handle)
 SELECT @SPID SPID, @line SQL

 FETCH NEXT FROM curSPID INTO @SPID
END

CLOSE curSPID
DEALLOCATE curSPID

DROP TABLE #SP_WHO2

GO