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

Per this http://msdn.microsoft.com/en-us/library/ms171931.aspx you're out of luck using the XML type in SQL CE. It simply does not exist. You still get a 2gb BLOB ntext field but that's no help.

select ... as xml works in a ce query but it looks like the Grid still truncates it as ntext. To test create a table named Test with char as nvarchar(4000) and text as ntext fields. Use the following statements to fill data and return it:

select char, text from test as xml

insert into test (char, text) values (replicate('A',4000), replicate('b',4000) + replicate('B',4000))

I get both fields truncating at roughly the same spot in grid mode which would suggest it truncates at much less than 65535. Removing the as xml has no effect so it isn't actually going to XML data anyway.

Your answers:

1. You're not allowed cast but convert is valid. Problem: No xml type in the engine. Possible long workaround: farm the data into a SQL Express database temp or otherwise will get the proper type. Not a small feat unless you can handle the overhead.
2. SSMS is getting extensible but I would say this is a non-starter. It is a .NET app and can be viewed in Reflector but that doesn't suggest much.
3. While the worst option it does seem like the easiest from an SSMS standpoint, sadly. You're still having to auto-resize each column but hopefully you wouldn't be breaking it up into more than 2-3.

At the end of the day, however, I can't help but think that all of these would still be a waste of time. Perhaps you could find a better SQL CE only tool that you can edit the schema and view every column in a proper manner. It's kinda sad that it would even come to this but I don't see an easier option.

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

Answer 3

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.