/*================================================
将查询的文本，插入数据表中，很有意思玩一玩
首先创建表： create table aa_temp01(id int identity(1,1),objname varchar(7999),value Nvarchar(4000))
然后执行：exec [sp1_helptext_aa2] 'sp1_helptext_aa2'
查看结果：select * from aa_temp01
    drop table aa_temp01
==========================================================*/

alter procedure [dbo].[sp1_helptext_aa2]
@objname nvarchar(776) --说明:参数只能为存储过程名称或视图名
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
,@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 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
--print 'dbname:'+@dbname
---select @objid = object_id(@objname)
select @objid=OBJECT_ID from sys.objects where name=@objname
--print @objid
if (@objid is null)
        begin
        raiserror(15009,-1,-1,@objname,@dbname)
        return (1)
        end
    begin
        /*
        **  Find out how many lines of text are coming back,
        **  and return if there are none.
        */
        if (select count(*) from sys.syscomments c, sys.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 sys.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 sys.syscomments where id = @objid and encrypted = 0
                ORDER BY number, colid
        FOR READ ONLY
    end
--print 'objid:'+convert(varchar,@objid)
/*
**  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
--print 'Line:'+@Line
IF @Line is NOT NULL
    INSERT #CommentText VALUES( @LineId, @Line )
    
---将查询到的文本写入到这个表里
insert into aa_temp01 
select @objname,Text from #CommentText order by LineId

CLOSE  ms_crs_syscom
DEALLOCATE     ms_crs_syscom

DROP TABLE     #CommentText
return (0) -- sp_helptext