SQLServer存储过程Print

SQLServer 存储过程，打印其他存储过程或函数对象的创建语句 代码来源于系统自带的存储过程 无 Create PROCEDURE [dbo].[sp_PrintProc] @objname NVARCHAR(776) , @columnname SYSNAME = NULLAS --打印对象 SET nocount ON DECLARE @dbname SYSNAME , @objid

SQLServer 存储过程，打印其他存储过程或函数对象的创建语句
代码来源于系统自带的存储过程

<无>

Create PROCEDURE [dbo].[sp_PrintProc]
    @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 2550. These need to all have  
** the same values. 2550 was selected in order for the max length  
** display using down level clients  
*/ ,
        @SyscomText NVARCHAR(4000) ,
        @Line NVARCHAR(2550)  
  
    SELECT  @DefinedLength = 2550  
    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(2550) 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  
  
    IF @Line IS NOT NULL 
        INSERT  #CommentText
        VALUES  ( @LineId, @Line )  
    DECLARE @printLine NVARCHAR(2550)
    DECLARE PostCur CURSOR FOR   
    SELECT Text FROM #CommentText ORDER BY LineId  
    OPEN PostCur  
    FETCH NEXT FROM PostCur INTO @printLine  
    WHILE @@fetch_status = 0 
        BEGIN  
            PRINT @printLine
            FETCH NEXT FROM PostCur INTO @printLine    
        END  
    CLOSE PostCur  
    DEALLOCATE PostCur   
    CLOSE  ms_crs_syscom  
    DEALLOCATE  ms_crs_syscom  
  
    DROP TABLE  #CommentText  
  
    RETURN (0) -- sp_PrintProc  

--存储过程查询
declare @StrSql varchar(max)
set @StrSql=(
Select 'exec('+char(39)+'sp_PrintProc '+name+char(39)+');' as [data()] From sys.objects where Type='P' and name like 'softManage_%' for xml path('')
)
exec(@StrSql)