Get the full text of a long request

Question

How to get the full text of the request from the following request:

SELECT t.[text]
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t

Here, with a sufficiently long request, the request text breaks off. I use SSMS 17 and 18 versions.

Answer 1

This can be due to the maximum amount of characters that the result to grid can return, 65535.

Starting from SSMS 18.2 you are able to change this.

Thanks Aaron Bertrand for pointing that out.

Aaron also mentions:

if 64kb is the limit you’re hitting, there are a lot of places where statement text is truncated long before that any way (see literally any execution plan).

The text datatype of sys.dm_exec_sql_text is nvarchar(max), no issues there.

You could cast the column to XML as a workaround

SELECT CAST(t.[text] AS XML)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t

Or a better method by Evgeniy Gribkov

SELECT t.[text] 
FROM sys.dm_exec_requests AS r 
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t 
FOR XML RAW, ELEMENTS;

Better this way: SELECT t.[text] FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t FOR XML RAW, ELEMENTS; Since the request text can not always be converted to XML. In particular, it cannot convert query text when remotely calling stored procedures: "XML parsing: line 13, character 129, illegal qualified name character"

Or save the results to a file

Thanks Erik Darling for linking some more related answers