As per Microsoft docs, dm_exec_sql_text
returns the text of the SQL batch that is identified by the specified sql_handle while dm_exec_input_buffer
returns information about statements submitted to an instance of SQL Server.
I used DBCC INPUTBUFFER
for getting the last executed SQL. But it seems both dm_exec_sql_text
and dm_exec_input_buffer
are replacement DBCC INPUTBUFFER
. If this is the case then what is the main difference between them? When should we use one of them?
CodePudding user response:
sys.dm_exec_input_buffer
represents only the batch or RPC call that began the request, in other words the command initiated by the client directly, not any later procedures or triggers called. As noted in the documentation, you can only pass a session_id
and request_id
. This is the equivalent of DBCC INPUTBUFFER
as you have noted.
Meanwhile, sys.dm_exec_sql_text
can be passed an sql_handle
or a plan_handle
, which can be got from various places, not least from sys.dm_exec_requests
, which means that you can get the text of the currently executing statement, not just the initial batch.