Steve and I were manning the community booth at TechEd 2005 when someone stopped by to ask about ideas for keeping a backup copy of their stored procedures in source control. If you've tried the version control in Enterprise Manager you'll know that it's perhaps not the friendliest thing in the world. My first thought was to recommend Bill Wunder's fine utility that will script everything out and check it into VSS. We talked about the options a bit and the user moved on, but I had made a note to see if I couldn't offer a simple solution, one that you could extend or modify easily as needed.

The code below is what I came up with. It almost worked on the first try, I forgot that objects might be owned by a domain user, something in the form "domain\username" and the extra backslash caused it to break. As I usually do, I coded this in VB6 to leverage the intellisense and strong type checking, then converted it to VBScript once I had it working the way I wanted. This particular implementation will script out the non-system stored procedures in all the user created databases, but you could easily modify it to script other objects or to only do one particular database, etc.

The scripting methods of DMO offer a ton of different options, I always like to have the script plus the permissions in one file so I used that. One option I deliberately excluded was the one that puts a header on the file. Using that would cause the header to change each time even though the code might not have changed.

Note that this code does not do anything with source control of any type, it just puts it on disk so you can back it up, check in it to source control, zip and email, whatever solution would make sense for you. The script below will work nicely in a standard job. Let me know if you run into problems or make interesting changes to it!

''THESE MUST BE SET AND THE AGENT ACCOUNT MUST HAVE WRITE ACCESS TO THE DRIVE SELECTED
FolderName = "Z:\SQLDATA\StoredProcedures"
ServerName = "."
 
EXCLUDE_LIST = "MASTER/MSDB/MODEL/TEMPDB/NORTHWIND/PUBS"
 
''do this once so we dont have to worry about it later
If Right(FolderName, 1) <> "\" Then
    FolderName = FolderName & "\"
End If
 
'we'll use this a few times
Set oFSO = CreateObject("Scripting.FileSystemObject")
 
'make sure this container folder exists
If oFSO.FolderExists(FolderName) = False Then
    oFSO.CreateFolder UCase(FolderName)
End If
 
'connect to the server instance using trusted credentials so we dont have password stored in a file and we dont have
'to worry about password changes breaking anything
Set oServer = CreateObject("SQLDMO.SQLServer")
With oServer
    .LoginSecure = True
    .Connect ServerName
End With
 
'loop through all databases and excluding those in the EXCLUDE_LIST above, script out all the stored procedures.
'You could easily change this to read from a table or to let you pass in a different list.
For Each oDB In oServer.Databases
 
    If InStr(1, EXCLUDE_LIST, ucase(oDB.Name)) = 0 Then
        
        'each db will get a folder in the main folder (FolderName) that will act as a container for the backup
        'folders we'll create each time we run this
        dbFolder = FolderName & UCase(oDB.Name) & "\"
        If oFSO.FolderExists(dbFolder) = False Then
            oFSO.CreateFolder dbFolder
        End If
        
        'script out all the non-system procs
        For Each oProc In oDB.StoredProcedures
            If oProc.SystemObject = False Then
  ''262150= SQLDMOScript_ObjectPermissions + SQLDMOScript_PrimaryObject + SQLDMOScript_OwnerQualify
                oProc.Script  262150,  dbFolder & "\" & replace(oProc.Owner, "\", "-") & "_" & replace(oProc.Name, "\", "-") & ".sql"
            End If
        Next
        
    End If
Next
 
'close it all out
oServer.DisConnect
Set oServer = Nothing
Set oFSO = Nothing