fn_get_sql() function can be used to get what statement is currently executing for a process. Usually we use DBCC INPUTBUFFER to get know what the process is doing. But this will return the procedure name not the exactly which code of line is being executed. For example, if you have procedure 1 and procedure 1 calls another procedure 2 and procedure 2 calls another procedure 3. And even when procedure 3 is executing, DBCC INPUTBUFFER will return procedure 1 not procedure 3.
fn_get_sql() will return the exact line of code being executed.
To see how it works, create following stored procedure. This procedure can be used to diagnose performance related issues, long running queries inside the procedures, blocked queries.
IF OBJECT_ID('dbo.WhatsGoingOn') IS NOT NULL
DROP PROCEDURE dbo.WhatsGoingOn
GO
CREATE PROCEDURE dbo.WhatsGoingOn
(
@SPID smallint,
@Wait tinyint = 0
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @NoLoop bit, @SqlHandle binary(20), @HandleFound bit
DECLARE @StmtStart int, @StmtEnd int
DECLARE @Command nvarchar(4000), @WaitStr varchar(8)
SET @HandleFound = 0
SET @NoLoop = 0
IF @Wait NOT BETWEEN 0 AND 60
BEGIN
RAISERROR('@Wait should be between 0 to 60 seconds', 16, 1)
RETURN -1
END
ELSE
BEGIN
SET @WaitStr = '00:00:' + RIGHT('00' + CAST(@Wait AS varchar(2)), 2) END
WHILE 1 = 1
BEGIN
SELECT @SqlHandle = sql_handle,
@StmtStart = stmt_start/2,
@StmtEnd = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
FROM master.dbo.sysprocesses
WHERE spid = @SPID
AND ecid = 0
IF(@SqlHandle = 0x0) OR (@StmtStart = 0 AND @StmtEnd = 0)
BEGIN
IF @HandleFound = 0
BEGIN
RAISERROR('Cannot find handle or the SPID is invalid', 16, 1)
RETURN -1
END
ELSE
BEGIN
RAISERROR('Query/Stored procedure completed', 0, 1)
RETURN 0
END
END
ELSE
BEGIN
SET @HandleFound = 1
END
SET @Command =
(
SELECT
SUBSTRING ( text,
COALESCE (NULLIF(@StmtStart, 0), 1),
CASE @StmtEnd
WHEN -1 THEN DATALENGTH(text)
ELSE (@StmtEnd - @StmtStart)
END
)
FROM ::fn_get_sql(@SqlHandle)
)
RAISERROR(@Command, 0, 1) WITH NOWAIT
IF @NoLoop = 1
BEGIN
RETURN 0
END
WAITFOR DELAY @WaitStr
END
END