Backup Stored Procedures

Script from a colleague.
This could be scheduled to backup code.
Particularly useful in development environments -

CREATE PROCEDURE dbo.StoredProcBackup_sp

@DatabaseName as varchar(200) = 'DATABASENAME IN HERE',
@DestinationDir as varchar(200) = 'D:\SQLDATA\MSSQL\BACKUP\StoredProcedures\AND THE REST OF YOUR PATH',
@User as varchar(20)= 'USER',
@Password as varchar(20) = 'PASSWORD'
AS

set nocount on

declare @dircreatecmd as varchar(210)
declare @Error int
declare @RootDir varchar(210)
declare @RootDay varchar(215)
declare @CheckDircmd varchar(300)

set @CheckDircmd = 'dir ' + @DestinationDir
EXEC @Error =  master..XP_CMDSHELL @CheckDircmd
If @Error = 1
 Begin
 --Directory does not exist so create it
 set @RootDir =  @DestinationDir
 set @dircreatecmd = 'mkdir ' + @RootDir
 EXEC master..XP_CMDSHELL @dircreatecmd
 End 
Declare @Day varchar(2)
Declare @Month varchar(2)
set @Day = convert(varchar(2),DATEPART(dd,getDate()))
set @Month = convert(varchar(2),DATEPART(mm,getDate()))

set @RootDay = @DestinationDir +'\'+ @Month + '\' + @Day + '\' 
declare @CheckRootDaycmd varchar(320)

set @CheckRootDaycmd = 'Dir ' + @RootDay
EXEC @Error =  master..XP_CMDSHELL @CheckRootDaycmd
If @Error = 1
 Begin
 --Directory does not exist so create it
 set @RootDir =  @RootDay
 set @dircreatecmd = 'mkdir ' + @RootDir
 EXEC master..XP_CMDSHELL @dircreatecmd
 End 

Declare @ProcName varchar(100)
Declare @ProcText varchar(8000)
Declare @filename varchar(104)
Declare @FilePath varchar(310)
DECLARE @cmd varchar (8000), @var varchar (8000)

-- backup all stored procs into single file.
EXEC master..xp_cmdshell 'osql -U?? -P?????? -Q"SELECT rtrim(ltrim(text))  FROM DATABASENAME.dbo.sysobjects so join DATABASENAME.dbo.syscomments sc on so.id = sc.id where type = ''p''" -dDATABASE -oD:\SQLDATA\MSSQL\BACKUP\StoredProcedures\REST OF YOUR PATH\AllStoredProcs_CURRENT.txt'


Declare BackupTables_cur cursor for

-- select all the stored procedures from the given database

SELECT name, text FROM DATABASENAME.dbo.sysobjects  so,  DATABASENAME.dbo.syscomments sc
where so.id = sc.id
and type = 'p'

Open BackupTables_cur

Fetch Next from BackupTables_cur into @ProcName, @ProcText
While @@Fetch_Status = 0
Begin
 Print @ProcName
 Print  @ProcText
 Print @Day
 Set @filename = @ProcName + '.txt'
 set @FilePath = @DestinationDir +'\'+ @Month + '\' + @Day + '\' + @filename

  
 DECLARE @FS int, @OLEResult int, @FileID int
 
 
 EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
 IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'
 
 --Open a file
 execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FilePath, 8, 1
 IF @OLEResult <> 0 PRINT 'OpenTextFile'
 
 --Write Text1
 execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @ProcText
 IF @OLEResult <> 0 PRINT 'WriteLine'
 
 EXECUTE @OLEResult = sp_OADestroy @FileID
 EXECUTE @OLEResult = sp_OADestroy @FS

 Fetch Next from BackupTables_cur into @ProcName, @ProcText
End
Close BackupTables_cur
Deallocate BackupTables_cur

set nocount off
GO