重構sql server的sys.sp_helptext存儲

来源:http://www.cnblogs.com/dzy863/archive/2016/09/23/5900127.html
-Advertisement-
Play Games

本文目錄列表: 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、參考清單列表

 
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 前幾天寫一個頁面 發現寬度為900px的div居然放不下3個寬度為300px的內聯元素li,只好改用了float:left來佈局。後來上網一查,才知道inline-block是有預設間距的,預設間距為4px,並且inline也是有預設間距。現在就來盤點一些解決inline-block元素和inlin ...
  • The Write Less , Do More ! jQuery選擇器 1. #id : 根據給定的ID匹配一個元素 結果: 這是第一個p標簽 這是第二個p標簽 2. element : 根據給定的元素標簽名匹配所有元素 結果: 這是div標簽1 這是div標簽2 這是p標簽 3. .class ...
  • 前面的話 前面分別介紹了拖拽模擬和磁性吸附,當可視區域記憶體在多個可拖拽元素,就出現碰撞檢測的問題,這也是javascript動畫的一個經典問題。本篇將詳細介紹碰撞檢測 原理介紹 碰撞檢測的方法有很多,接下來使用九宮格分析法 假設黃色元素要與紅色元素進行碰撞。將紅色元素所處的區域分為9部分,自身處於第 ...
  • SQLSERVER編譯與重編譯 編譯的含義 當SQLSERVER收到任何一個指令,包括查詢(query)、批處理(batch)、存儲過程、觸發器(trigger) 、預編譯指令(prepared statement)和動態SQL語句(dynamic SQL Statement)要完成語法解釋、語句解 ...
  • 將Mahout on Spark 中的機器學習演算法和MLlib中支持的演算法統計如下: 主要針對MLlib進行總結 分類與回歸 分類和回歸是監督式學習; 監督式學習是指使用有標簽的數據(LabeledPoint)進行訓練,得到模型後,使用測試數據預測結果。其中標簽數據是指已知結果的特征數據。 分類和回 ...
  • SQL Server的IO性能受到物理Disk的IO延遲和SQL Server內部執行的IO操作的影響。在監控Disk性能時,最主要的度量值(metric)是IO延遲,IO延遲是指從Application創建IO請求,到Disk完成IO請求的時間延遲。如果物理Disk不能及時完成IO請求,跟不上請求 ...
  • CREATE DATABASE statement not allowed within multi-statement transaction. 剛開始報這個錯誤的時候,我上度娘搜了一下。 別人是在Sql Server 管理界面新增數據的時候,報的錯誤。 而我,是在ASP.NET MVC程式啟動, ...
  • Oracle ORA-07445 evaopn2()+128錯誤問題 問題描述 Plsql developer執行一段sql報錯: 經查alert log詳細報錯信息為: ORA-07445: exception encountered: core dump [evaopn2()+128] [SIG... ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...