Edit

sys.dm_exec_input_buffer (Transact-SQL)

  • Article

Applies to: SQL Server 2014 Azure SQL Database Not supported. Azure Synapse Analytics Not supported. Analytics Platform System (PDW)

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.

Important

Running this DMV outside of SQL Server Management Studio against SQL Server without VIEW SERVER STATE permissions (such as in a trigger, stored procedure, or function) throws a permission error on the master database.

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.

Important

Running this DMV outside of SQL Server Management Studio against Azure SQL Database without owner permissions (such as in a trigger, stored procedure, or function) throws a permission error on the master database.

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

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 user sessions.

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.is_user_process = 1;
GO

See Also