Example of refactoring SQL Server's Sys.helptext storage

1, the function and effect of sys.helptext storage

Recent approaches to the realistic programmable object definition body provided by SQL Server include: sys.syscomments (view), Sys.all_sql_modules (sys.sql_modules) (view), object_ Definition (functions) and Sys.helptext (storage). There is time to write a blog post for different ways. This article mainly studies the display effect of Sys.helptext, it feels somewhat not very good. Look at the actual effect of the storage in the following figure:

The above figure now see nothing, then the following picture of the Text field column content copied into a separate file to see the effect of the following figure:

Here's my red rectangular box callout. Each row adds a display effect such as char (13) and char (10) that, if changed based on this result, increases the length of the programmable object definition (mainly char (13) and char (10)).

2, refactoring Sys.helptext storage (named Dbo.usp_helptext) to provide intuitive results

Found the Sys.helptext display effect, I feel not very satisfied, then refactor it. The code after the refactoring is as follows:

If object_id (N ' dbo.usp_helptext ', ' P ') is not NULL
Begin
drop procedure [dbo]. [Usp_helptext];
End
Go

CREATE PROCEDURE [dbo]. [Usp_helptext]
(
@objname nvarchar (776)
, @columnname sysname = NULL
, @keeporiginal bit = NULL
)
As
Begin
SET NOCOUNT ON

Set @keeporiginal = ISNULL (@keeporiginal, 1);

declare @dbname sysname
, @objid int
, @BlankSpaceAdded int
, @BasePos int
, @CurrentPos int
, @TextLength int
, @LineId int
, @AddOnLen int
, @LFCR int--lengths of line feeds carriage return
, @DefinedLength int

/* Note:length of @SyscomText is 4000 to replace the Length of
* * Text column in syscomments.
* * lengths on @Line, #CommentText Text column and
* * value for @DefinedLength are all 255. These need to all have
* * the same values. 255 is selected in order for the max length
* * Display using down level clients
*/
, @SyscomText nvarchar (4000)
, @Line nvarchar (255)

Select @DefinedLength = 255
Select @BlankSpaceAdded = 0/*keeps track of blank spaces at end of of lines. Note Len function ignores
Trailing Blank spaces*/
CREATE TABLE #CommentText
(LineId int
, Text nvarchar (255) collate Catalog_default)

/*
* * Make sure the @objname are local to the current database.
*/
Select @dbname = ParseName (@objname, 3)
If @dbname is null
Select @dbname = db_name ()
else if @dbname <> db_name ()
Begin
RAISERROR (15250,-1,-1)
Return (1)
End

/*
* * If @objname exists.
*/
Select @objid = object_id (@objname)
if (@objid is null)
Begin
RAISERROR (15009,-1,-1, @objname, @dbname)
Return (1)
End

--If second parameter was given.
If (@columnname is not null)
Begin
--Check If it is a table
if (select COUNT (*) from sys.objects where object_id = @objid and type in (' S ', ' U ', ' TF ') =0
Begin
RAISERROR (15218,-1,-1, @objname)
Return (1)
End
--Check if it is a correct column name
if (SELECT ' Count ' =count (*) from sys.columns where name = @columnname and object_id = @objid) =0)
Begin
RAISERROR (15645,-1,-1, @columnname)
Return (1)
End
if (ColumnProperty (@objid, @columnname, ' iscomputed ') = 0)
Begin
RAISERROR (15646,-1,-1, @columnname)
Return (1)
End

DECLARE ms_crs_syscom CURSOR Local
For select text from syscomments where id = @objid and encrypted = 0 and number =
(select column_id from sys.columns where name = @columnname and object_id = @objid)
ORDER BY Number,colid
For READ only

End
else if @objid < 0--Handle system-objects
Begin
--Check count of rows with text data
if (select COUNT (*) from master.sys.syscomments where id = @objid and text are not null) = 0
Begin
RAISERROR (15197,-1,-1, @objname)
Return (1)
End

Declare ms_crs_syscom CURSOR Local to select text from master.sys.syscomments where id = @objid
Order by number, colid to READ only
End
Else
Begin
/*
* * Find out how many lines of the text are coming back,
* * and return if there are none.
*/
if (select COUNT (*) from syscomments c, sysobjects o where o.xtype isn't in (' S ', ' U ')
and o.id = c.id and o.id = @objid) = 0
Begin
RAISERROR (15197,-1,-1, @objname)
Return (1)
End

if (select COUNT (*) from syscomments where id = @objid and encrypted = 0) = 0
Begin
RAISERROR (15471,-1,-1, @objname)
Return (0)
End

DECLARE ms_crs_syscom CURSOR Local
For select text from syscomments where id = @objid and encrypted = 0
Order by number, colid
For READ only

End

/*
* * Else get the text.
*/
Select @LFCR = 2
Select @LineId = 1

Open ms_crs_syscom

FETCH NEXT from ms_crs_syscom into @SyscomText

While @ @fetch_status >= 0
Begin
Select @BasePos = 1
Select @CurrentPos = 1
Select @TextLength = LEN (@SyscomText)

While @CurrentPos!= 0
Begin
--looking for end of line followed by carriage
Select @CurrentPos = CHARINDEX (char (+char), @SyscomText, @BasePos)

--IF carriage return found
IF @CurrentPos!= 0
Begin
/*if new value for @Lines length would be > then the
**set length then insert current contents of @line
**and proceed.
*/
while (LEN (@Line), 0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength
Begin
Select @AddOnLen = @DefinedLength-(IsNull (LEN (@Line), 0) + @BlankSpaceAdded)
Insert #CommentText values
(@LineId,
IsNull (@Line, n ") + IsNull (SUBSTRING (@SyscomText, @BasePos, @AddOnLen), n")
Select @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
End

--annotation system used to modify the original use as follows
--select @Line = IsNull (@Line, n ") + IsNull (SUBSTRING (@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), n '")
Select @Line = IsNull (@Line, N ') + IsNull (SUBSTRING (@SyscomText, @BasePos, @CurrentPos-@BasePos + (case @keeporiginal When 1 THEN @LFCR ELSE 0 "), N")
Select @BasePos = @CurrentPos +2
Insert #CommentText values (@LineId, @Line)
Select @LineId = @LineId + 1
Select @Line = NULL
End
Else
--else carriage return not found
Begin
IF @BasePos <= @TextLength
Begin
/*if new value for @Lines length would be > then the
**defined length
*/
while (IsNull (LEN (@Line), 0) + @BlankSpaceAdded + @TextLength-@BasePos +1) > @DefinedLength
Begin
Select @AddOnLen = @DefinedLength-(IsNull (LEN (@Line), 0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
(@LineId,
IsNull (@Line, n ") + IsNull (SUBSTRING (@SyscomText, @BasePos, @AddOnLen), n")
Select @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
End
Select @Line = IsNull (@Line, n ') + IsNull (SUBSTRING (@SyscomText, @BasePos, @TextLength-@BasePos +1), n ')
If LEN (@Line) < @DefinedLength and Charindex (", @SyscomText, @TextLength +1) > 0
Begin
Select @Line = @Line + ', @BlankSpaceAdded = 1
End
End
End
End

FETCH NEXT from ms_crs_syscom into @SyscomText
End

IF @Line is not NULL
INSERT #CommentText VALUES (@LineId, @Line)

Select Text from #CommentText ORDER by LineId

Close ms_crs_syscom
Deallocate ms_crs_syscom

DROP TABLE #CommentText

Return (0)--sp_helptext
End
Go

I have already marked the above changes, other sources sys.helptext content.
Then look at the reconstructed effect, as shown in the following figure:

The above shows and can not see what is different from the sys.helptext, continue to speak text content replication into a separate file for the effect of the following figure:

The Red rectangular box on the

is the effect of the display, and the next section is for comparison, which can be used to display the following code:
EXEC [sys].[ Sp_helptext]
     @objname = N ' Sys.fn_get_sql '    --nvarchar (776)
 & nbsp; , @columnname = NULL--sysname
Go
 
EXEC [dbo].[ Usp_helptext]
     @objname = N ' Sys.fn_get_sql '    --nvarchar (776)
    , @columnname = NULL--sysname
   , @keeporiginal = 1--bit
Go
 

Note: Dbo.usp_helptext is compatible with sys.helptext functionality.

3, Sys.helptext and Dbo.usp_helptext limitations and solutions

Read the source code of Sys.helptext and its corresponding online help document, found that its output field column text of up to 255 double-byte characters per line, its output to the client end size is 4,000 double-byte characters, this can be encoded program (such as VS program Read access, etc.) to break through this limit.

The biggest disadvantage is that there are 255 rows per line, and it is possible to encounter a delimiter in a row before the previous part belongs to the preceding 255 double-byte characters, and the latter part belongs to the first part of the second 255 double-byte character.
The specific test code is as follows:
IF object_id (N ' [dbo].[ Uvm_mytestview] ', ' V ') is not NULL
BEGIN
DROP VIEW [dbo]. [Uvm_mytestview];
End
Go

CREATE VIEW [dbo]. [Uvm_mytestview]
As
SELECT
1 as N ' Col_ 1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 2 As [Col_ 2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222 222]
Go

The results show the following figure:

The effect shown in the illustration on the

appears to be truncation. To eliminate this limitation, use the function object_definition (although this has its drawbacks, it will be a separate explanation for the difference between the content defined by the output programmable object).
&NBSP
4, summary
 
Learning the system objects provided by SQL Server, discovering that the code they write is really tight, that many specifications and exception handling, and so on, have learned a lot, but also found that some SQL The internal limits of the server are not written, such as the maximum 8060-byte limit of the table record row and the addition of a 24-byte pointer to the original record row after the side length overflow to the data page of the Row-overflow index-allocation type, which also has the ability to exceed the maximum 8060 bytes of the row. Maybe I see too little theory. Only by continuing to Excel, code programming will continue, and sometimes the limit of the maximum 4,000 double-byte characters exported by the SQL Server client can be solved programmatically.
 
Yesterday saw a blog post in the garden print, sp_helptext restrictions and extensions printing an extra long string through print output, and you also encounter an identifier truncation problem, because print always adds char ( 13) and char (10), which may split an identifier into two batches before and after.