sql server 快速查询存储过程/函数内容

本文主要是介绍sql server 快速查询存储过程/函数内容，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

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  

这篇关于sql server 快速查询存储过程/函数内容的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！