Occassionaly you’ll run into a point where you aren’t getting enough characters in the Text output of SSMS. Fortunately; there’s a quick thing you can do (within TSQL), and you don’t have to mess around with setting the results to Text Output either through the SSMS GUI, or CTRL+T.
You simply add a single line at the end of your statement.
FOR XML PATH(''), TYPE
You can do this after any statement and see what kind of results you get. They will be displayed in another Tab. You’ll get the XML syntax with that but there’s another way you can do it below.
use master; set nocount on select name from sys.databases for xml path(''), type
Or… If you want the results in straight text without all the XML syntax ( that you can simply copy and paste into something else ), you can put the results into a variable, and select the variable SELECT @MyVariable, and the xml path will list only text after you click on the link. Like so…