MSSQL sp_helptextplus

最新推荐文章于 2021-11-18 10:22:40 发布

weixin_30267697

最新推荐文章于 2021-11-18 10:22:40 发布

阅读量108

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/zwheui/p/6251981.html

本文介绍了一个名为sp_HelpTextPlus的存储过程，用于解决SQL Server中sp_helptext命令显示存储过程文本时可能产生的截断问题。通过创建自定义存储过程，能够确保即使面对过长的代码行也能完整地展示出来。

默认的sp_helptext 如果存储过程每行代码太长会自动截断

把这个sp_helptextplus添加到SSMS的keyboard快捷键里面

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE PROC sp_HelpTextPlus @SPNAME NVARCHAR(255)
AS
    BEGIN
        DECLARE @objname NVARCHAR(776) = @SPNAME;

        DECLARE @ObjectText NVARCHAR(MAX)= '';
        DECLARE @SyscomText NVARCHAR(MAX);
        DECLARE @LineLen INT;
        DECLARE @LineEnd BIT = 0;
        DECLARE @CommentText TABLE
            (
              LineId INT IDENTITY(1, 1) ,
              Text NVARCHAR(MAX) COLLATE catalog_default
            );

        DECLARE ms_crs_syscom CURSOR LOCAL
        FOR
            SELECT  text
            FROM    sys.syscomments
            WHERE   id = OBJECT_ID(@objname)
                    AND encrypted = 0
            ORDER BY number ,
                    colid FOR READ ONLY

        OPEN ms_crs_syscom
        FETCH NEXT FROM ms_crs_syscom INTO @SyscomText

        WHILE @@fetch_status >= 0
            BEGIN
                SET @LineLen = CHARINDEX(CHAR(10), @SyscomText);
                WHILE @LineLen > 0
                    BEGIN

                        SELECT  @ObjectText += LEFT(@SyscomText, @LineLen) ,
                                @SyscomText = SUBSTRING(@SyscomText,
                                                        @LineLen + 1, 4000) ,
                                @LineLen = CHARINDEX(CHAR(10), @SyscomText) ,
                                @LineEnd = 1;
             
                        INSERT  INTO @CommentText
                                ( Text )
                        VALUES  ( @ObjectText )

                        SET @ObjectText = '';
                    END

                IF @LineLen = 0
                    SET @ObjectText += @SyscomText;
                ELSE
                    SELECT  @ObjectText = @SyscomText ,
                            @LineLen = 0;

                FETCH NEXT FROM ms_crs_syscom INTO @SyscomText
            END

        CLOSE  ms_crs_syscom;
        DEALLOCATE    ms_crs_syscom;

        INSERT  INTO @CommentText
                ( Text )
                SELECT  @ObjectText;

        SELECT  text
        FROM    @CommentText
        ORDER BY LineId;

    END
GO

转载于:https://www.cnblogs.com/zwheui/p/6251981.html