Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Save Your Stored Procedures

By Andy Warren,

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
Total article views: 12667 | Views in the last 30 days: 2
 
Related Articles
FORUM

script to change the port number

script to change the port number

FORUM

Sql Script to change the Login Permission

Sql Script to change the Login Permission

FORUM

Changing db owner script

need to change multiple databases on many servers

BLOG

SCRIPT: Basic Information About Indexes

I’m having a little fun with documenting basic information about indexes in my current project. I’m ...

FORUM

Script for changing the port number & disable Named Pipes protocol

Script for changing the port number & disable Named Pipes protocol

Tags
programming    
sql-dmo    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones