66535, but who’s counting?

If you want to look at the description of a view (and that is the field name where in the selection code is stored) you can select the view, open DESIGN and see the code that selects the columns, with all the alias and the joins and froms, oh my.  Maybe

We have some views where that just doesn’t work the way you want.  You can see it, but when I try to select all\copy\paste into a text editor, and all the stuff doesn’t come over.  OK, so i put the cursor in from of the first word, and use the down arrow to select the text, REBAR style.  At some point, the “copy” select goes off and you can’t select the text.  A couple of times, i have been able to copy half and then select the rest, but there are times when that doesn’t work either.

So – how about this:

EXEC sp_helptext 'dbo.myView';

Yup, that works, but in my case, i get 1049 rows of text of varying lenghts.  REALLY ugly.

How about this:

USE [Ntier_MIPU]
GO
SELECT definition,*
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(‘PM.vwGenPatInfo’);

you get the object_id, the definition field, which contains selecta view myView as select … but in my case, the definition is truncated.  There is a character limit for how long the field can be in grid-view.  I had no better (in fact, worse) luck with text view and exactly the same data if i output it to file.

In  Management Studio – Tools, Options, Query Results, SQL Server, Results To Grid, Maximum Characters Retrieved – Non XML Data, there is a setting for the Maximum Characters Retrieved, which can not be set to more than 64K.  You can of course select more data to a table, but the display is limited to the aforementioned 66535 characters.

And for XML RAW was similarly ugly and not terribly readable.  Maybe i could have opened it in MS Internet Explorer or such like with a style sheet that would not show the elements and attributes but that was WAY too much work, given the ease of my workaround.

If you want to build, or duplicate, a big table — i mean a table with a complex schema — you can right-click on the table in SSMS, Script Table as, create to … whatever (file, agent, query window).  Yes, query window.  So find the view, script to create in the query window, trim off the create view front stuff and the closing quote, and you have it.

Advertisements