重构sql server的sys.sp

本文目录列表：

2、重构sys.sp_helptext存储（命名为dbo.usp_helptext)提供直观的效果

3、sys.sp_helptext和dbo.usp_helptext的限制以及解决方案

1、sys.sp_helptext存储的功能和效果

近来在研究sql server提供的现实可编程对象定义体的方法包括：sys.syscomments(视图)、sys.all_sql_modules(sys.sql_modules)(视图)、object_definition(函数)和sys.sp_helptext(存储)。针对以上方式的不同以后有时间在写成博文。本文主要研究了sys.sp_helptext的显示效果，感觉有些不太美好。先看该存储的现实效果如下图：

上图现在看没有什么的，那就将如下图的Text字段列内容复制放入单独的文件中再看其效果如下图：

上图我红色矩形框标注的地方了吧，每个行后都增加了char(13)和char(10)这两个字符导致的这样的显示效果，如果按照这个结果为基础进行变更，就增加了可编程对象定义的长度（主要是char(13)和char(10))。

2、重构sys.sp_helptext存储（命名为dbo.usp_helptext)提供直观的效果

发现了sys.sp_helptext的显示效果，我自己感觉不太满意，那么就重构嘛。重构后的代码如下:

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 feed 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 was 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 lines. Note Len function ignores

                                    trailing blank spaces*/

    CREATE TABLE #CommentText

    (LineId    int

        ,Text  nvarchar(255) collate catalog_default)

    /*

    **  Make sure the @objname is 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

    /*

    **  See 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 is not null) = 0

                begin

                    raiserror(15197,-1,-1,@objname)

                    return (1)

                end

            declare ms_crs_syscom CURSOR LOCAL FOR select text from master.sys.syscomments where id = @objid

                ORDER BY number, colid FOR READ ONLY

        end

    else

        begin

            /*

            **  Find out how many lines of text are coming back,

            **  and return if there are none.

            */

            if (select count(*) from syscomments c, sysobjects o where o.xtype not 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 return

            select @CurrentPos =   CHARINDEX(char(13)+char(10), @SyscomText, @BasePos)

            --If carriage return found

            IF @CurrentPos != 0

            begin

                /*If new value for @Lines length will be > then the

                **set length then insert current contents of @line

                **and proceed.

                */

                while (isnull(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

                -- 注释系统原来的使用如下修改

                --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 END)), 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 will 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

以上修改之处我已经标注了，其他的均来源sys.sp_helptext内容。

那就看看重构后的效果，如下图：

以上显示并看不出和sys.sp_helptext的有何不同，继续讲Text内容复制放入单独为文件中效果如下图：

上图红色矩形框就是显示的效果，下部分是为了对比，这部分可以使用如下代码显示器效果：

EXEC [sys].[sp_helptext]

     @objname = N'sys.fn_get_sql'    -- nvarchar(776)

    ,@columnname = NULL -- sysname

GO

EXEC [dbo].[usp_helptext]

     @objname = N'sys.fn_get_sql'    -- nvarchar(776)

    ,@columnname = NULL -- sysname

    ,@keeporiginal = 1 -- bit

GO

注意：dbo.usp_helptext兼容了sys.sp_helptext的功能。

3、sys.sp_helptext和dbo.usp_helptext的限制以及解决方案

查阅了sys.sp_helptext的源码和其对应的联机帮助文档，发现其输出的字段列Text每行最多255个双字节字符，其输出到客户端最终的大小是4000个双字节字符，这个可以通过编码程序（例如VS程序读取获取等）突破这个限制。

其最大的缺点是每行255个，有可能遇到一行中一个分隔符前一部分属于前一个255个双字节字符，后一部分属于后一个255双字节字符的前部分。

具体的测试代码如下：

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_1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111', 2 AS [Col_2222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222]

GO

效果展示如下图：

上图显示的效果就是出现了截断。消除这个限制那就使用函数object_definition(不过这个也有缺点的，以后才单独讲解输出可编程对象定义的内容的区别)。

4、总结语

学习sql server提供的系统对象，发现他们写的代码真的很严密的，很多规范以及异常处理等，确实学到了很多的，不过也发现有些sql server的内部限制是不写出来的，如表记录行最大8060字节的限制以及可边长长度溢出到row-overflow索引分配类型的数据页后也在原来的记录行中增加24字节的指针这样也可有能超过行最大8060字节的限制。可能我看理论太少的缘故吧。唯有继续精进，代码编程还是要继续的，有时候sql server客户端输出的最大4000个双字节字符的限制可以通过编程的方式得到完本的解决。

昨天看到园中的一篇博文print、sp_helptext的限制与扩展通过PRINT输出分批次打印超长的字符串，也会遇到某个标识符截断的问题，因为PRINT每次到打印到客户端总增加了char(13)和char(10)这两个字符，这样就可能将一个标识符分割为前后两个批次。

5、参考清单列表

巴特西

重构sql server的sys.sp_helptext存储

最新文章

热门文章