View all ntext column text in SQL Server Management Studio for SQL CE database

Question

I often want to do a "quick check" of the value of a large text column in SQL Server Management Studio (SSMS). The maximum number of characters that SSMS will let you view, in grid results mode, is 65535. (It is even less in text results mode.) Sometimes I need to see something beyond that range. Using SQL Server 2005 databases, I often used the trick of converting it to XML, because SSMS lets you view much larger amounts of text that way:

SELECT CONVERT(xml, MyCol) FROM MyTable WHERE ...

But now I am using SQL CE, and there is no Xml data type. There is still a "Maximum Characters Retreived XML" value under Options; I suppose this is useful when connecting to other data sources.

I know I can just get the full value by running a little console app or something, but is there a way within SSMS to see the entire ntext column value?

[Edit] OK, this didn't get much attention the first time around (18 views?!). It's not a huge concern, but maybe I'm just obsessed with it. There has to be some good way around this, doesn't there? So a modest bounty is active. What I am willing to accept as answers, in order from best-to-worst:

1. A solution that works just as easy as the XML trick in SQL CE. That is, a single function (convert, cast, etc.) that does the job.
2. A not-too-invasive way to hack SSMS to get it to display more text in the results.
3. An equivalent SQL query (perhaps something that creatively uses SUBSTRING and generates multiple ad-hoc columns??) to see the results.

The solution should work with nvarchar and ntext columns of any length in SQL CE from SSMS. Any ideas?

Answer 1

If you work with CE 3.5 supporting TOP and OUTER APPLY, try this statement:

SELECT texts.id, SUBSTRING(texts.text_column, number*100+1, 100)
FROM 
    (SELECT texts.id, texts.text_column, (99+LEN(texts.text_column))/100 AS l 
     FROM texts) AS texts
OUTER APPLY 
    (SELECT TOP(l) number 
     FROM master.dbo.spt_values val 
     WHERE name IS NULL 
     ORDER BY number) n

100 (and 99) is the text length I used for testing in my data.

Answer 2

This may not be ideal, but can you break it up while viewing it in text mode?

For example:

SELECT
     SUBSTRING(my_text, 1, 8000),
     SUBSTRING(my_text, 8001, 8000)
FROM
     dbo.My_Table

It works as-is in SQL 2008. For 2005 you might need to do a CAST to VARCHAR(MAX) and I'm not sure if that would work as expected or not.

I just came up with the following query in 2008 (I don't have a CE version handy) and it worked well for a mostly unknown length of text (limit of 800,000 characters I think, which you probably don't want to just display anyway). The chunks come out as separate rows, so you may have carriage returns in the middle of your text because of that. I don't know if that's an issue or not.

;WITH my_cte AS
(
    SELECT
        SUBSTRING(my_text, 1, 8000) AS sub,
        SUBSTRING(my_text, 8001, LEN(CAST(my_text AS VARCHAR(MAX)))) AS remainder
    FROM
        dbo.Test_Text
    UNION ALL
    SELECT
        SUBSTRING(remainder, 1, 8000),
        SUBSTRING(remainder, 8001, LEN(remainder))
    FROM
        my_cte
    WHERE
        LEN(remainder) > 0
)
SELECT
    *
FROM
    my_cte