4

Is there a way to get the last executed SQL Server command without the use of DBCC INPUTBUFFER?

For example, is there a System View or Catalog that contains this information?

Thanks.

Neil Weicher
  • 2,370
  • 6
  • 34
  • 56

2 Answers2

2

You can pass your SPID (SQL Process ID) to the following: 

DECLARE @sql_handle VARBINARY(128);

SELECT @sql_handle = sql_handle
FROM sys.sysprocesses
WHERE spid = @@SPID; --you can pass a different SPID here

SELECT [text]
FROM sys.dm_exec_sql_text(@sql_handle);
Oreo
  • 529
  • 3
  • 16
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • 3
    While this may work for many situations, it is not the same. When the client calls a stored procedure, DBCC INPUT BUFFER will return the stored procedure and the parameters it was called with, but sys.dm_exec_sql_text will return the text that created the stored procedure. – Scott Dec 09 '14 at 16:53
0

Yes, from SQL Server 2014 SP4 and newer, you can use the following DMV:

IF OBJECT_ID('sys.dm_exec_input_buffer') IS NOT NULL --we are running SQL 2014 SP4 or newer!
BEGIN
    SELECT b.*
    FROM sys.dm_exec_input_buffer(@@SPID, NULL) b;
END
ELSE --we are running SQL 2014 SP3 or older...
BEGIN
    CREATE TABLE #TraceInfo (
        EventType NVARCHAR(255)
        ,parameters INTEGER
        ,event_info NVARCHAR(4000)
        );

    INSERT INTO #TraceInfo
    EXEC ('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS');

    SELECT t.*
    FROM #TraceInfo t;
END
Oreo
  • 529
  • 3
  • 16