sp_configure 'show advanced options', 1; 

go

RECONFIGURE

GO 

sp_configure 'Ole Automation Procedures', 1; 

GO 

RECONFIGURE; 

GO 

 

Then, run this script. Note that you’ll what to change the line that points to the text file to be created

go

-- =============================================

-- 

-- =============================================

-- 1/1/1900 created

IF EXISTS (SELECT name 

    FROM   sysobjects 

    WHERE  name = N'sp_SPs' 

    AND    type = 'P')

    DROP PROCEDURE sp_SPs

GO

CREATE PROCEDURE sp_SPs 

--  sp_SPs 

AS

set nocount on

--runs all the stored procs and triggers 

--in the current db to a text file

DECLARE 

    @vchrFile VARCHAR(100) ,

    @vchrFileID INT ,

    @FS INT ,

    @RC INT ,

    @vchrStoredProcName varchar(8000),

    @vchrChar varchar(1),   

    @vchrLine varchar(8000),

    @intPos int,

    @vchrPrevChar varchar(1),

    @intAscii int,

    @intPrevAscii int,

    @vchrSysCommentText varchar(8000)

SET @vchrFile = 'c:\temp\SPs.vb'

--  sp_SPs 

CREATE TABLE #proc

    (

    linetext text NULL,

    RowID int 

    ) 

--================================================

-- open the output file

--================================================

EXEC @RC = sp_OACreate 'Scripting.FileSystemObject', @FS OUT

IF @RC <> 0 begin

    PRINT 'Error:  Creating the file system object'

    return

end

-- Opens the file specified by the @vchrFile input parameter

EXEC @RC = sp_OAMethod @FS , 'OpenTextFile' , 

    @vchrFileID OUT , @vchrFile , 2 , 1

--  sp_SPs 

-- Prints error if non 0 return code during sp_OAMethod 

--OpenTextFile execution 

IF @RC <> 0 begin

    PRINT 'Error opening ' + @vchrFile

    return

end

-- write the first line

EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , 

    'Public Partial Class SPs'

-- Prints error if non 0 return code during sp_OAMethod 

--WriteLine execution

IF @RC <> 0 begin

    PRINT 'Error:  Writing string data to file'

    return

end

--=====================================================

--gather data on stored procedure into table _dbText

--=====================================================

DECLARE curStoredProcs CURSOR KEYSET FOR 

    SELECT  Name from sysobjects 

        where ([name] like 'sp%' 

            and xtype = 'P')

        order by Name

OPEN curStoredProcs

FETCH NEXT FROM curStoredProcs INTO @vchrStoredProcName

WHILE (@@fetch_status = 0) BEGIN

    --initialize

    select 

        @vchrLine = '',

        @vchrPrevChar = ''

    truncate table #proc    

    --print @vchrStoredProcName

    insert into #proc exec sp_class2 @vchrStoredProcName

    -- =============================================

    -- loop through one stored proc

    -- =============================================

    DECLARE curComments  CURSOR LOCAL FOR 

        select linetext

            from #proc

            order by RowID

    OPEN curComments

    FETCH NEXT FROM curComments into @vchrSysCommentText

    --loop through the lines in the syscomments table. 

    --there can be one or many for the stored proc, 

    --many stored proc lines can be on one syscomments line

    WHILE @@fetch_status >= 0

    BEGIN

--print @vchrSysCommentText

--  sp_SPs 

        -- Appends the string value line to the file 

        --specified by the @vchrFile input parameter

        EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', 

            Null , @vchrSysCommentText

        -- Prints error if non 0 return code during 

        --sp_OAMethod WriteLine execution

        IF @RC <> 0 begin

            PRINT 'Error:  Writing string data to file'

            return

        end

        FETCH NEXT FROM curComments into @vchrSysCommentText

    END

    CLOSE curComments

    DEALLOCATE curComments

    FETCH NEXT FROM curStoredProcs INTO @vchrStoredProcName

END

CLOSE curStoredProcs

DEALLOCATE curStoredProcs

-- write the first line

EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , 'End Class'

-- Prints error if non 0 return code during sp_OAMethod 

--WriteLine execution

IF @RC <> 0 begin

    PRINT 'Error:  Writing string data to file'

    return

end

EXECUTE @RC = sp_OADestroy @vchrFileID

EXECUTE @RC = sp_OADestroy @FS

go

grant exec on sp_SPs to public