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.
DBCC INPUTBUFFER for getting the last executed SQL. But it seems both
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?
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
request_id. This is the equivalent of
DBCC INPUTBUFFER as you have noted.
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.