你的位置:首页 > 数据库

[数据库]重构sql server的sys.sp_helptext存储


本文目录列表:
1、sys.sp_helptext存储的功能和效果
2、重构sys.sp_helptext存储(命名为dbo.usp_helptext)提供直观的效果
3、sys.sp_helptext和dbo.usp_helptext的限制以及解决方案
4、总结语
5、参考清单列表
 
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 NULLbegin  drop procedure [dbo].[usp_helptext];endgo create procedure [dbo].[usp_helptext](   @objname nvarchar(776)  ,@columnname sysname = NULL  ,@keeporiginal bit = NULL)asbegin  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_helptextendgo

 



以上修改之处我已经标注了,其他的均来源sys.sp_helptext内容。
那就看看重构后的效果,如下图:


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


上图红色矩形框就是显示的效果,下部分是为了对比,这部分可以使用如下代码显示器效果:
EXEC [sys].[sp_helptext]   @objname = N'sys.fn_get_sql'  -- nvarchar(776)  ,@columnname = NULL -- sysnameGO EXEC [dbo].[usp_helptext]   @objname = N'sys.fn_get_sql'  -- nvarchar(776)  ,@columnname = NULL -- sysname  ,@keeporiginal = 1 -- bitGO

 


 
注意: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 NULLBEGIN  DROP VIEW [dbo].[uvm_MyTestView];ENDGO   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、参考清单列表
  • https://msdn.microsoft.com/en-us/library/ms176112.aspx
  • http://www.cnblogs.com/fishparadise/p/4797539.html