sys.dm_exec_input_buffer (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2014 SP2)yesAzure SQL DatabasenoAzure 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)