2014년 5월 8일 목요일

sp_helptext 버그 수정

스토어드 프로시져나 뷰의 소스를 보기 위해서는 sp_helptext 를 사용합니다.
근데 sp_helptext에는 사소한 버그가 하나 있습니다.
소스를 작성할때 한줄에 255자 이상 작성하면 강제로 줄바꿈이 되어 버립니다.
이게 문법에 딱 맞게 줄바꿈이 되면 상관이 없는데 255자가 넘어가면 바로 줄바꿈이 되버리는 바람에 오브젝트명이 중간에 잘리기도 합니다.
마
치
이렇
게...
그래서 그대로 다시 alter를 하면 에러가 발생하기도 합니다.

sp_helptext도 저장프로시져 입니다.
그래서 sp_helptext 명령으로 소스를 볼 수 있습니다.
exec sp_helptext sp_helptext
다음은 sp_helptext의 소스의 첫 부분입니다.
create procedure sys.sp_helptext
@objname nvarchar(776)
,@columnname sysname = NULL
as

set nocount on

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)
잘 살펴보면 255가 3번 나옵니다. nvarchar의 최대길이가 4000이기 때문에 이걸 4000으로 바꿔줍니다. 4000자 이상은 어떻게 하냐고?? 그러지 말자... -_-;; 이렇게 수정한 소스로 sp_helptext를 수정해주면 줄바꿈 버그는 사라집니다. 그런데 한가지 문제가 있는데 시스템 저장 프로시져는 alter 할 수가 없습니다. DAC로 연결해서 수정하면 된다는데 저는 다른 이름으로 프로시져를 생성해서 사용합니다. 다음 소스는 제가 소스를 수정한 sp_helptextX인데요. 약간의 기능을 좀 더 넣었습니다. 다른 개발자가 만들어 놓은 스토어드 프로시저의 소스를 보다보면 소스의 앞부분이나 뒷부분에 엔터를 잔뜩 처 넣었다거나 줄끝에 공백이나 탭이 잔뜩 있는 겨우가 있습니다. 아무 문제 없지만 저는 어쩐지 지저분해보여서 싫었습니다. 그래서 그런 부분들을 정리해주는 기능을 넣었습니다. 편집증 다음은 sp_helptextx의 전체 소스입니다.
create procedure sp_helptextX
@objname nvarchar(776)
,@columnname sysname = NULL
as

set nocount on

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(4000)

select @DefinedLength = 4000
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(4000) collate database_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 @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

CLOSE  ms_crs_syscom
DEALLOCATE  ms_crs_syscom

IF @Line is NOT NULL INSERT #CommentText VALUES( @LineId, @Line )

/* ------------------------------ SCRIPT 정리 시작 ------------------------------ */

/* 줄 끝 공백과 탭 제거 */
declare @seq int
declare @text nvarchar(4000)
declare @crlf nvarchar(2)

DECLARE ScriptCursor1 CURSOR LOCAL FORWARD_ONLY STATIC FOR
    select LineId, Text from #CommentText order by LineId

OPEN ScriptCursor1

FETCH NEXT FROM ScriptCursor1 INTO @seq, @text

WHILE @@FETCH_STATUS = 0 BEGIN
    IF @text LIKE '%' + CHAR(13) + CHAR(10) BEGIN
        SET @text = SUBSTRING(@text, 1, LEN(@text) - 2)
        SET @crlf = CHAR(13) + CHAR(10)
    END ELSE BEGIN
        SET @crlf = ''
    END
    SET @text = REVERSE(@text)
    WHILE 1 = 1 BEGIN
        IF CHARINDEX(CHAR(9), @text) != 1 AND CHARINDEX(CHAR(32), @text) != 1 BREAK
        IF CHARINDEX(CHAR(9), @text) = 1 SET @text = SUBSTRING(@text, 2, 4000)
        IF CHARINDEX(CHAR(32), @text) = 1 SET @text = SUBSTRING(@text, 2, 4000)
    END
    SET @text = REVERSE(@text) + @crlf
    UPDATE #CommentText SET [Text] = @text WHERE LineId = @seq
    FETCH NEXT FROM ScriptCursor1 INTO @seq, @text
END

CLOSE ScriptCursor1
DEALLOCATE ScriptCursor1

/* SCRIPT 끝 케리지 리턴 제거 */
SET @seq = NULL
SET @text = NULL

DECLARE ScriptCursor2 CURSOR LOCAL FORWARD_ONLY STATIC FOR
    select LineId, Text from #CommentText order by LineId DESC

OPEN ScriptCursor2

FETCH NEXT FROM ScriptCursor2 INTO @seq, @text

WHILE @@FETCH_STATUS = 0 BEGIN
    IF @TEXT != CHAR(13) + CHAR(10) BREAK

    DELETE #CommentText WHERE LineId = @seq

    FETCH NEXT FROM ScriptCursor2 INTO @seq, @text
END

CLOSE ScriptCursor2
DEALLOCATE ScriptCursor2

/* SCRIPT 앞 케리지 리턴 제거 */
SET @seq = NULL
SET @text = NULL

DECLARE ScriptCursor3 CURSOR LOCAL FORWARD_ONLY STATIC FOR
    select LineId, Text from #CommentText order by LineId

OPEN ScriptCursor3

FETCH NEXT FROM ScriptCursor3 INTO @seq, @text

WHILE @@FETCH_STATUS = 0 BEGIN
    IF @TEXT != CHAR(13) + CHAR(10) BREAK

    DELETE #CommentText WHERE LineId = @seq

    FETCH NEXT FROM ScriptCursor3 INTO @seq, @text
END

CLOSE ScriptCursor3
DEALLOCATE ScriptCursor3

/* ------------------------------ SCRIPT 정리 끝 ------------------------------ */

/* SCRIPT PRINT */
SET @seq = NULL
SET @text = NULL

DECLARE ScriptCursor9 CURSOR LOCAL FORWARD_ONLY STATIC FOR
    select LineId, Text from #CommentText order by LineId

OPEN ScriptCursor9

FETCH NEXT FROM ScriptCursor9 INTO @seq, @text

WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT @text

    FETCH NEXT FROM ScriptCursor9 INTO @seq, @text
END

CLOSE ScriptCursor9
DEALLOCATE ScriptCursor9

DROP TABLE  #CommentText

return (0) -- sp_helptext

댓글 2개:

  1. 감사합니다. 잘 쓰겠습니다.

    답글삭제
  2. Wynn Slots for Android and iOS - Wooricasinos
    A wooricasinos.info free app for slot machines from WRI Holdings Limited that lets you worrione play the aprcasino popular games, such as free video febcasino.com slots, table games and live herzamanindir casino

    답글삭제