How-to export to text file all user written stored procedures in a sql server 2005 database

-- COURTESY OF :
-- Steve Gray

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'GetAllProcs'
AND type = 'P')
DROP PROCEDURE GetAllProcs
GO

CREATE PROCEDURE GetAllProcs



AS




DECLARE
@vchrFile VARCHAR(1000) ,
@vchrFileID INT ,
@FS INT ,
@RC INT ,
@vchrStoredProcName varchar(8000),
@vchrChar varchar(1), -- holds the current character that we are evaluating
@vchrLine varchar(8000),-- holds the line that we are about to print
@intPos int,
@vchrPrevChar varchar(1),
@intAscii int,
@intPrevAscii int,
@vchrSysCommentText varchar(8000)



--initialize

SET @vchrFile = 'C:\temp\export.sql'


--===================================================================================
-- open the output file
--===================================================================================
EXEC @RC = sp_OACreate 'Scripting.FileSystemObject', @FS OUT

IF @RC <> 0
PRINT 'Error: Creating the file system object'

-- Opens the file specified by the @vchrFile input parameter
EXEC @RC = sp_OAMethod @FS , 'OpenTextFile' , @vchrFileID OUT , @vchrFile , 8 , 1

-- Prints error if non 0 return code during sp_OAMethod OpenTextFile execution
IF @RC <> 0
PRINT 'Error: Opening the specified text file'

--===================================================================================
--gather data on stored procedure into table _dbText
--===================================================================================
DECLARE curStoredProcs CURSOR KEYSET FOR
select s.name from sysobjects s where type = 'U' order by Name
/*
-- COPY PASTE THE DESIRED TYPES OF OBJECTS ABOVE
--HOW-TO LIST ALL PROCEDURE IN A DATABASE
-- select s.name from sysobjects s where type = 'P' order by Name
--HOW-TO LIST ALL TRIGGERS BY NAME IN A DATABASE
-- select s.name from sysobjects s where type = 'TR' order by Name
--HOW-TO LIST TABLES IN A DATABASE
select s.name from sysobjects s where type = 'U' order by Name
--how-to list all system tables in a database
-- select s.name from sysobjects s where type = 's' order by Name
--how-to list all the views in a database
-- select s.name from sysobjects s where type = 'v' order by Name
*/
OPEN curStoredProcs

FETCH NEXT FROM curStoredProcs INTO @vchrStoredProcName

WHILE (@@fetch_status = 0) BEGIN
set @vchrLine = '-- ####################################################################'
EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
IF @RC <> 0 PRINT 'Error: Writing string data to file'

set @vchrLine = ''
EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
IF @RC <> 0 PRINT 'Error: Writing string data to file'

set @vchrLine = '-- ***** ' + @vchrStoredProcName + ' ***** '
EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
IF @RC <> 0 PRINT 'Error: Writing string data to file'

set @vchrLine = ''
EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
IF @RC <> 0 PRINT 'Error: Writing string data to file'

set @vchrLine = '-- ####################################################################'
EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
IF @RC <> 0 PRINT 'Error: Writing string data to file'

--initialize
select
@vchrLine = '',
@vchrPrevChar = ''

-- =============================================
-- loop through one stored proc
-- =============================================
DECLARE curComments CURSOR LOCAL FOR
SELECT [text] FROM syscomments WHERE id = OBJECT_ID(@vchrStoredProcName) and encrypted = 0
ORDER BY number, colid
FOR READ ONLY

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
--initialize
select
@intPos = 1

WHILE @intPos <> len(@vchrSysCommentText) BEGIN
select @vchrChar = substring(@vchrSysCommentText,@intPos,1)
select @intAscii = ascii(@vchrChar)

if not (@intAscii = 13 or @intAscii = 10)
select @vchrLine = @vchrLine + @vchrChar

--if we encounter a line feed...
if @intAscii in (10,13) and @intPrevAscii in (10,13) begin
--output a line and clear the line buffer

-- Appends the string value line to the file specified by the @vchrFile input parameter
EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine

-- Prints error if non 0 return code during sp_OAMethod WriteLine execution
IF @RC <> 0
PRINT 'Error: Writing string data to file'

select @vchrLine = ''
end


select @intPos = @intPos + 1,
@vchrPrevChar = @vchrChar,
@intPrevAscii = @intAscii

END

FETCH NEXT FROM curComments into @vchrSysCommentText
END

CLOSE curComments
DEALLOCATE curComments


set @vchrLine = ''
EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
IF @RC <> 0 PRINT 'Error: Writing string data to file'
set @vchrLine = ''
EXEC @RC = sp_OAMethod @vchrFileID, 'WriteLine', Null , @vchrLine
IF @RC <> 0 PRINT 'Error: Writing string data to file'


FETCH NEXT FROM curStoredProcs INTO @vchrStoredProcName
END

CLOSE curStoredProcs
DEALLOCATE curStoredProcs

EXECUTE @RC = sp_OADestroy @vchrFileID
EXECUTE @RC = sp_OADestroy @FS