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:
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.
A not-too-invasive way to hack SSMS to get it to display more text in the results.
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?
