Working as I do on Data Warehouses which are built by a build system that uses meta data about the external source systems, I sometimes find myself writing procedures and functions that create SQL scripts as their output. Such as for instance an ETL procedure for an external system. Occasionally these generate very large scripts that exceed the 65,535 (grid) or 8,192 (text) limit for the results pane. Which can make debugging them a real pain, as the best way to debug them, I find, is have the output for a known execution go to the results tab in SSMS and then paste this into another window in SSMS to see what the script looks like.

Well there is it seems a simple answer, typically my return type will be either varchar(max) or nvarchar(max), if you cast this to XML then the maximum size returned via SSMS is much larger, up to unlimited in fact.