How to retrieve a large text from inside a table?

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] 
GO
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))

GO
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]
               ,[i]
               ,[activity]
               ,[script])
    SELECT
               DT=cast ('3 may 2018' as date)
               ,I=1100
               ,ACTIVITY='MOVE APPLICATION ROWS - the Ids work'
               ,[script]=BulkColumn 
    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]
      ,[i]
      ,[activity]
      ,[script]
  FROM [MY_DBA_SERVER].[DBA].[dbo].[activity]
GO

I can see that the stuff I have just inserted is on my table.

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

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

and that gives me this:

My problem is that I cannot retrieve the whole content of my scrip

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

StackExchange Database Administrators Q#205720, answer score: 8