for my own records I have a table in one of my servers where I like to save my activities and scripts

the table definition is:

    IF OBJECT_ID('[dbo].[activity]') IS NOT NULL 
    DROP TABLE [dbo].[activity] 
    CREATE TABLE [dbo].[activity] ( 
    [dt]        DATE                             NOT NULL,
    [i]         SMALLINT                         NOT NULL,
    [activity]  NVARCHAR(max)                     NOT NULL,
    [script]    NVARCHAR(max)                     NULL  
               CONSTRAINT [DF__activity__script__6E01572D] DEFAULT (NULL))
    CREATE CLUSTERED INDEX [pk_activity] 
       ON [dbo].[activity] ([dt] desc, [i] desc)

as this table is located in the `DBA` database on my `DBA_SERVER` server,
from my local machine I created a `linked server` to the `DBA_SERVER` so that I can insert scripts into my table.

this is how I insert a script into my table, the script is located on my local machine:

        INSERT INTO [MY_DBA_SERVER].[DBA].[dbo].[activity]
                   DT=cast ('3 may 2018' as date)
                   ,ACTIVITY='MOVE APPLICATION ROWS - the Ids work'
        FROM Openrowset( Bulk 'C:\Users\MMiorelli\Downloads\applicationID consolidated.sql', 
                         Single_Blob) as [the script]

and this seems to be working fine, because when I run the select below:

    SELECT [dt]
      FROM [MY_DBA_SERVER].[DBA].[dbo].[activity]

I can see that the stuff I have just inserted is on my table.
[![enter image description here][1]][1]

and when I run the following script I find out how much data is in each field:

    SELECT [dt]
          ,len_activity=DATALENGTH( [activity])
          ,len_script=DATALENGTH( [script])
      FROM [MY_DBA_SERVER].[DBA].[dbo].[activity]

and that gives me this:

[![enter image description here][2]][2]

My problem is that I cannot retrieve the whole content of my script.
I have tried to copy it using the mouse as you can see on the picture below:

[![enter image description here][3]][3]

But I have some hairy scripts that are very long, and copying like that cuts them short of their whole.

my question is:

How to retrieve this `script` from my table?

it can be via select or even saving it to a file, 
I would prefer a T-SQL way of doing it if possible.

Just a curiosity
would that be the same way for images?

Top Answer
meme (imported from SE)
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 ( 
    	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
Answer #2
aaron bertrand (imported from SE)
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.

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.