You have the report's number and now need the statement behind it. Capturing the SQL behind SSMS reports lets you inspect the batch and adapt the useful parts. A tightly scoped Extended Events session makes that investigation practical on a test instance.
Treat the Report as a Query Client
SSMS standard reports submit SQL to the connected server. Extended Events can observe the completed batches from that client. The captured text helps explain the report's source views, filters, and calculations. It does not automatically turn the batch into a polished administrative script.
I capture a report on a test instance first. That gives me room to inspect the batch without filling a production session with unrelated activity. Close extra SSMS windows or add a session-specific filter when practical. Several open query windows share similar application names.
The example uses sql_batch_completed and a client application-name prefix matching SQL Server Management Studio. Some requests arrive as RPC activity rather than batches. An empty or incomplete capture means checking the request type, not declaring that the report runs no SQL. The report's front panel is tidy. The submitted batch has no obligation to be equally tidy.
Capturing the SQL behind SSMS reports gives you the actual request, including the report's filters and parameter choices.
Create a Session to Capture SQL Behind SSMS Reports
Run the next block with permission to manage server-scoped Extended Events sessions. Choose an unused session name. The ring_buffer target keeps a bounded amount of event data in memory, which is convenient for a small inspection without preparing an event-file folder.
The event captures batch_text as its event payload and adds the application, session identifier, and sql_text actions. The predicate restricts the client application family. That is a starting filter, not a guarantee that only one report appears. Other matching SSMS activity can still be captured.
I leave STARTUP_STATE off because this is a temporary investigation. Start the session only when ready to reproduce the report. Its memory limit also means older records can disappear or XML can be truncated. Keep the reproduction short and read the result promptly. A diagnostic session should have a clear owner and end condition.
CREATE EVENT SESSION SSMSReportCapture ON SERVER
ADD EVENT sqlserver.sql_batch_completed
(
ACTION(sqlserver.client_app_name,sqlserver.session_id,sqlserver.sql_text)
WHERE([sqlserver].[like_i_sql_unicode_string]
([sqlserver].[client_app_name],N'Microsoft SQL Server Management Studio%'))
)
ADD TARGET package0.ring_buffer(SET max_memory=4096)
WITH(MAX_MEMORY=4096 KB,STARTUP_STATE=OFF);
ALTER EVENT SESSION SSMSReportCapture ON SERVER STATE=START;
Run One Report and Keep Its Context
In Object Explorer, select the test database and open a standard report such as Disk Usage. Wait for the report to finish before reading the completed-batch events. Record which database and report you used, because the captured SQL can depend on that context.
Avoid typing additional investigative queries into several matching windows during the capture. They become noise in the same ring buffer. If the report opens a separate connection, inspect its session details and consider tightening the filter on the next run. Do not assume your original query window's session identifier is the report's identifier.
Which calculation are you trying to understand? Identify that field before collecting a long set of batches. The objective is a specific useful query, not a complete archive of SSMS activity. Retain the relevant batch and its report context, then stop collecting. This also limits the amount of unrelated query text handled during the review.
Read the SQL Behind SSMS Reports From the Ring Buffer
sys.dm_xe_session_targets exposes the active target data. Convert it to XML and expand the event nodes. The following query extracts timestamp, session action, application action, and batch_text from each event. Keep the event text and actions separate so their sources remain clear.
The target is live while the session runs. Its contents can change between reads, and a busy capture can overwrite older events. Save the relevant output through your normal local evidence process before stopping the session. Do not rely on the ring buffer as durable storage.
If the report produced no matching batch event, check the application name and whether RPC events are needed. Add a tightly scoped rpc_completed event for a second test if appropriate. Diagnose the capture method before assuming the server hid the report SQL. An event filter that matches nothing is still a perfectly functioning filter.
WITH TargetData AS
(
SELECT TRY_CONVERT(xml,t.target_data) AS TargetXML
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t ON t.event_session_address=s.address
WHERE s.name=N'SSMSReportCapture' AND t.target_name=N'ring_buffer'
)
SELECT e.n.value('@timestamp','datetime2') AS EventTime,
e.n.value('(action[@name="session_id"]/value)[1]','int') AS SessionID,
e.n.value('(action[@name="client_app_name"]/value)[1]','nvarchar(256)') AS ApplicationName,
e.n.value('(data[@name="batch_text"]/value)[1]','nvarchar(max)') AS BatchText
FROM TargetData
CROSS APPLY TargetXML.nodes('/RingBufferTarget/event') AS e(n)
ORDER BY EventTime;Adapt the Query Without Copying Its Assumptions
Read the captured batch before executing it elsewhere. Reports can include version checks, temporary structures, multiple result sets, and formatting calculations intended for the report renderer. Identify the actual source query and the assumptions surrounding it.
Replace hard-coded database context and report-only filters deliberately. Keep documented system views and verify the columns available on your target version. A batch captured from one engine and SSMS version is not a universal contract for every installation.
Validate the adapted result against the report under the same conditions. Check units, rounding, database scope, and object categories. A number that differs only because one query reports reserved pages and another reports used pages is not automatically wrong. Preserve those definitions in the script's output labels. The useful adaptation explains the metric rather than merely reproducing a visually similar number.
Remove the Session When the Inspection Ends
Read and retain the useful evidence before stopping the session, because the active ring-buffer data is not a permanent event archive. The following cleanup stops collection and drops the temporary definition. Confirm the name carefully when several diagnostic sessions exist.
On a busy production instance, use a stricter scope and an approved short capture window if this method is required. Broad SSMS capture creates noise and can include unrelated administrative text. A ring buffer is suitable for a small test, while a bounded event-file design serves longer investigations better.
Keep the adapted SQL and its validation notes after cleanup. Capturing the SQL behind SSMS reports is valuable when it turns an opaque report calculation into a repeatable, documented check. Finish by removing the collection session and preserving only the evidence and script needed for that check.
ALTER EVENT SESSION SSMSReportCapture ON SERVER STATE=STOP;
DROP EVENT SESSION SSMSReportCapture ON SERVER;Related reading on this blog: Capturing Stored Procedure Executions with Extended Events in SQL Server and SQL Profiler vs Extended Events.
A report number is not the whole explanation, it is the output of a query you can inspect.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
