Export SPROC query to text file in SQL Server

sometime i have need to documented my Stored Procedured query

often i'm using sp_helptext SPROC_NAME then copy paste the result in notepad and save it. It consume alot of time if u using alot SPROC

found another way around using sqlcmd command, something like

DECLARE @sql AS NVARCHAR(1000)
SET @sql = 'sqlcmd -S (local) -d "DB_NAME" -q "sp_helptext SPROC_NAME" -o 'OUTPUT_FILE'

exec master..xp_cmdshell @sql

after some tweaking :D, i decided to wrap in SPROC

/* ===========================================================
Author: eRiCk WiDyA
Description: Export SPROC syntax to text file
Date: 10 Feb 2010

Usage: exec ExportSPROCToTextFile 'C:\', '[DB NAME]', 'sp_some%'
Param:
@FilePath : must exists first before run
@DatabaseName : use []
@SPROCCriteria : SPROC name that contains those words
===========================================================
*/

CREATE PROC ExportSPROCToTextFile

@FilePath AS NVARCHAR(50),
@DatabaseName AS NVARCHAR(50),
@SPROCCriteria AS NVARCHAR(50)

AS
SET NOCOUNT ON

DECLARE @sql AS NVARCHAR(1000)

SET @sql =
'USE ' + @DatabaseName + '

DECLARE @temp TABLE
(AutoID INT IDENTITY(1,1) PRIMARY KEY,
SPROCName NVARCHAR(50))

DECLARE @Init AS INT,
@Count AS INT,
@SPROCName AS NVARCHAR(50),
@Sql AS NVARCHAR (1000),
@DbName AS NVARCHAR (50)

SET @DbName = ''' + @DatabaseName + '''
SELECT @DbName = REPLACE(@DbName, ''['', '''')
SELECT @DbName = REPLACE(@DbName, '']'', '''')

SET @Init = 1
SET @Count = 0

INSERT INTO @temp (SPROCName)
SELECT ROUTINE_NAME
FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ''PROCEDURE'' AND
PATINDEX(''' + @SPROCCriteria + ''', ROUTINE_NAME) <> 0

SELECT @Count = SUM(1) FROM @Temp

WHILE @Init <= @Count
BEGIN
SELECT @SPROCName = SPROCNAME
FROM @temp
WHERE AutoID = @Init

SET @Sql = ''sqlcmd -S (local) -d "'' + @DbName + ''" -q "sp_helptext '' + @SPROCName + ''" -o ' + @FilePath + ''' + @SPROCNAME + ''.sql''

EXEC xp_cmdshell @Sql

SET @Init = @Init + 1
END'

EXEC (@sql)

GO

PS: i think it for SQL SERVER 2005 and above

see u