sys.dm_exec_input_buffer (Transact-SQL)

APPLIES TO: yesSQL Server (starting with 2014 SP2) yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

Returns information about statements submitted to an instance of SQL Server.

Syntax

sys.dm_exec_input_buffer ( session_id , request_id )

Arguments

session_id
Is the session id executing the batch to be looked up. session_id is smallint. session_id can be obtained from the following dynamic management objects:

request_id
The request_id from sys.dm_exec_requests. request_id is int.

Table Returned

Column name Data type Description
event_type nvarchar(256) The type of event in the input buffer for the given spid.
parameters smallint Any parameters provided for the statement.
event_info nvarchar(max) The text of the statement in the input buffer for the given spid.

Permissions

On SQL Server, if the user has VIEW SERVER STATE permission, the user will see all executing sessions on the instance of SQL Server; otherwise, the user will see only the current session.

On SQL Database, if the user is the database owner, the user will see all executing sessions on the SQL Database; otherwise, the user will see only the current session.

Remarks

This dynamic management function can be used in conjunction with sys.dm_exec_sessions or sys.dm_exec_requests by doing CROSS APPLY.

Examples

A. Simple example

The following example demonstrates passing a session id (SPID) and a request id to the function.

SELECT * FROM sys.dm_exec_input_buffer (52, 0);
GO

B. Using cross apply to additional information

The following example lists the input buffer for sessions with session id greater than 50.

SELECT es.session_id, ib.event_info   
FROM sys.dm_exec_sessions AS es  
CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib  
WHERE es.session_id > 50;
GO

See Also

Execution Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_exec_sessions (Transact-SQL)
sys.dm_exec_requests (Transact-SQL)
DBCC INPUTBUFFER (Transact-SQL)