Displaying Entire VARCHAR(MAX) from a SQL Query
-
I'm using VARCHAR(MAX) to hold dynamic TSQL that I'm generating. (generating DDL or say a trigger for auditing from the SYS tables)
Along the way I'm formatting it nicely and it could be upwards of about 3,000 lines.
I'd like to be able to see this, save it (as it looks in the variable) or get to it. It's nice to look over and sometimes vital when there is a bug.
I can get it all in a single line selecting it in grid mode. 3,000 lines of SQL in a single line is not pretty.
Changing the query results to Text mode doesn't help much. It's formatted nicely for a few lines and that's it.
Print Statement works perfectly up to a point, then it cuts off. Ideally the Print command would just print all of the text. It's useful for shorter lengths of code.
the best hack I've used is to use print statements and keep it in multiple smaller substrings. The Query Messages can display a lot of data, apparently just so much in one PRINT.
If there is an easy way to get this variable displayed as formatted, that would be great. I'm open to a method that writes this to HTML or something along those lines.
The best solution I can think of would be to write a windows app that runs the code for the dynamic sql and displays it in a large textbox in the application. I can do this if it's really the best method, but I'd like to handle it in SSMS if possible.
-
What do you have the max text output set to in Options in SSMS?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
That might be something.
Results to Grid: 65535 characters. Seems to be the max.
Results to Text: 256. Explains the short text. I can set this to a max of 8192. I'm sure that'll help SELECT to TEXT.
Helps a little, but it's about where the Print statement is. I'm sure I could do a similar hack with multiple selects selecting substrings of up to 8000 characters each.
I think I just found a new feature I hadn't seen before. On ResultsToGrid, right click on the grid and the last option is "Script Grid Results" which seems to script out a creation of the grid, so my entire VARCHAR(MAX) is there in full text. It's not quite as clean as just getting the results of the variable as you have to remove a little DDL around it to get to the created SQL, but it's better than my earlier hack.
-
SSMS is not the ideal tool to display varchar(max) fields, as GSquared was implying:
one of the settings it uses for increasing perforamnce can also prevent the full text of a varchar(max) from displaying;
by default it is set to show 256 chars, you may need to increase it yourself:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell (7/5/2011)
SSMS is not the ideal tool to display varchar(max) fields, as GSquared was implying:one of the settings it uses for increasing perforamnce can also prevent the full text of a varchar(max) from displaying;
Thank you. Yes, even at max value, it doesn't display the full varchar(MAX), apparently just a VARCHAR(8000).
SSMS is a great tool, but one of it's primary roles is being able to display results from tables/queries. I understand if default options are set to optimize the users experience and that I might have to do an extra step or change settings to get this to work correctly, but it should be possible in some way. The data is there behind the text, the window is capable of displaying massive amounts of text and SSMS is capable of sending megabytes of information from a single result (the XML function has a far higher capability).
I somehow imagine wrapping my dynamic SQL in an XML Tag and sending it through as an XML result.
-
Keep in mind that varchar(max) can be 2 Gig of data. That's approximately 2-billion characters. A standard novel is about 1,000 characters per page, so you're looking at a million pages of text.
With that in mind, it shouldn't be at all surprising that they don't really support displaying "all of varchar(max)" in SSMS.
If you need to see that kind of data, I recommend some other interface.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply