How to retrieve a large text from inside a table?

There's another way similar to Aaron's, using the little-documented processing-instruction... thing.

Using this gives you unchecked XML, which will won't replace predefined entities.

Also, the column will have XML identifying marks at the beginning and end, which may be another drawback depending on how you want to use the output.

But it'll be clicky and show you the whole thing!

CREATE TABLE dbo.LongText ( 
    Id INT IDENTITY, 
    LongText_N NVARCHAR(MAX), 
    LongText_X XML  
    );

INSERT dbo.LongText ( LongText_N, LongText_X )
SELECT asm.definition, (SELECT asm.definition AS [processing-instruction(_)] FOR XML PATH(''))
FROM sys.all_sql_modules AS asm

SELECT * FROM dbo.LongText AS lt

You can't copy from text output, because it truncates the data.

You can't copy from grid output, not only because it truncates the data, but it also mangles carriage returns, line feeds, and other control characters.

You might be able to convert that column to XML, then double-click to view the data in a new tab. I describe this here, but for a different purpose:

• Validate the contents of large dynamic SQL strings

Basically, SSMS wasn't made to do the task you want to do. You can extract to a file, but it's quite likely that similar truncation will happen during that process.

Your best bet is to pull the data from Powershell or C# and make that write the output to a file.

You should be able to accomplish this with the Stored Procedure I provided in the following answer (here on DBA.SE):

How to output more than 4000 characters in sqlcmd

Since you are using NVARCHAR(MAX), please see the note towards the bottom of that answer that has a link to the NVARCHAR version of that Stored Procedure on PasteBin.

In either case, the Stored Procedures print the output in chunks to get around the 4000 or 8000 character limitation (depending on NVARCHAR or VARCHAR, respectively) of PRINT, hence you shouldn't notice any truncation and you should still get newlines, etc working as expected, which won't happen when copying / pasting from the results grid.