本文目錄列表: 1、sys.sp_helptext存儲的功能和效果 2、重構sys.sp_helptext存儲(命名為dbo.usp_helptext)提供直觀的效果 3、sys.sp_helptext和dbo.usp_helptext的限制以及解決方案 4、總結語 5、參考清單列表 1、sys.s ...
本文目錄列表: 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 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、參考清單列表
- https://msdn.microsoft.com/en-us/library/ms176112.aspx
- http://www.cnblogs.com/fishparadise/p/4797539.html