Join after sys.dm_exec_sessions AS s → LEFT OUTER JOIN sys.dm_exec_requests
Text retrieval → OUTER APPLY sys.dm_exec_sql_text(r.sql_handle)
Input buffer retrieval → OUTER APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id)
The correct drag-and-drop choices are based on how blocking-chain details are normally assembled in Azure SQL Database.
The CTE must read from sys.dm_exec_requests because the alias er is used with er.session_id and er.blocking_session_id, and those columns come from sys.dm_exec_requests. Microsoft documents that sys.dm_exec_requests returns information about executing requests and includes the blocking_session_id column used to identify blockers.
After FROM sys.dm_exec_sessions AS s, the correct join is LEFT OUTER JOIN sys.dm_exec_requests so the query can still return sessions from sys.dm_exec_sessions even when a current request row is missing. This is useful when showing sessions that are blocked or blocking, while still attempting to attach current request details when available.
For batch text, use OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) because Microsoft documents sys.dm_exec_sql_text(sql_handle) as the function that returns the SQL batch text for the specified sql_handle.
For the input buffer, use OUTER APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id) because Microsoft documents that sys.dm_exec_input_buffer takes session_id and request_id and returns event_info, which is commonly used when sys.dm_exec_sql_text is null or when you want the last command text.
So the completed code uses:
LEFT OUTER JOIN sys.dm_exec_requests
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle)
OUTER APPLY sys.dm_exec_input_buffer(r.session_id, r.request_id)
