sp_helptext (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_helptext(nvarchar @objname
, nvarchar @columnname)

MetaData:

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