Introduction

This shows a method for generating scripts for existing SQL Server database routines so they can be added to a third-party version control management system (VCMS). In this case the version control system used is PVCS Version Manager, though the script could be amended for use with any VCMS. In this method, a stored procedure was written first to generate text output for all routines, and then a vbscript script was written to split the text into separate files, one for each routine.

Version control is a critical aspect of software development, but I find that often database code is not as well version controlled as normal application code. Use of a third party system allows rigorous version control with labelling, branching, merging, rolling-back, audit-trails, difference viewing, authorisation and all other features included in a comprehensive system. For one project I was assigned to there was no single place where stored procedure scripts could be maintained. Developers would get the stored procedure code directly from the development database or old scripts amend it and then put it in a new script, which would then be put in version control. This would then be rolled out to system test, user acceptance test and finally production. One of the databases had over 600 routines; it would have been quite a task to add each one as separate module to a version control system unless it could be automated.

Requirements

1. The script must implement the new version of the stored procedure whether or not it already exists, this rules out just having a CREATE or an ALTER statement.
2. Database stored procedures must not be dropped and recreated as different users have different rights assigned to the stored procedures, these rights would be lost if a stored procedure is dropped so the script must CREATE the procedure if it does not already exist and ALTER it if it does.
3. There must be a database with the current version of all routines that need to be scripted and added to the version control system.
4. It must be able to generate scripts for all routines in one go including functions and triggers.
5. Each routine must be saved as a separate script which can be added to source control.

How

sp_helptext is a very useful stored procedure and running it gives the CREATE source code to a routine. Example usage: EXEC sp_helptext 'myStoredProcedureName'. By calling this function from within a stored procedure, it is possible to write a stored procedure that will generate the create/alter scripts required for version control.

PVCS Version Manager allows some special keywords/tokens to be entered into source code, which it will use to maintain and populate values. These include $Revision:$, $Log:$ and $Workfile:$. You might notice that the procedure has these broken in parts; this is so they are not updated up when this stored procedure is stored on PVCS.

The script is generated in the 'messages' tab of SQL Server Management Studio. To save a script it is necessary to copy the messages output to the clipboard and then paste it into an ANSI text document.

There are two main purposes of this routine: the addition of the entire database source code to scripts and the addition of individual routines to scripts. If I have a new script to write I edit it directly on the database and when finished I just run the stored procedure with a parameter with the procedure name to create a formatted script ready to add directly to version control.

Instructions to generate script of entire database's routine source code

The steps are:

1. Install the stored procedure list above on the database you wish to document.

2. run this procedure:

EXEC INFGenerateObjectScript '%',1,1,1

3. The contents of the messages tab in SQL Server Management Studio will now contain a complete listing of all routines on the database. This needs to be copied to the clipboard and then saved to a text file in ANSI or if using SQL Server Management Studio Codepage 1252 will do (make sure it is not Unicode).

You will notice that there are some comments surrounding the beginning and end of each stored procedure with lines beginning -- %%START%% and -- %%END%%. These are used by a vbscript program as markers to separate each routine into a separate source file.

4. Both the vbscript file (attached) and the large text file containing all stored procedures that was generated by the stored procedure need to be copied to an empty directory. To generate the database scripts simply go into Windows Explorer and drag the file containing all the procedures onto the vbscript file and let go. This will pass in the filename as a parameter and split it into multiple files. Alternatively it can be called from a batch file or the DOS command line like this:

cscript ChopProcedures.vbs "C:\myDir\myRoutines.txt"

In the example above the files should be copied to the directory C:\myDir\ and the filename of the file containing all routines is myRoutines.txt.

5. Copy all the files that have just been generated into a directory and add them to PVCS.

Instructions for generating the database source code for an individual routine

In SQL Server Management Studio run the command

EXEC INFGenerateObjectScript 'myProcedureName'

Copy and paste the script into a new text file and save it as an ANSI file. Add this directly to PVCS.

Conclusion

Hopefully this article is useful and helps some people to store their code in a third-party version control system or at least give them an insight into a method for controlling database source code alongside other related application source code.

Disclaimer and Warnings and Information:
The stored procedure and vbscript are provided "as is" without warranty of any kind I will not be held responsible for any damage that you do to any system using it. It has been tested and used on SQL Server 2005, hopefully it will work on other versions of SQL Server but it hasn't been tested on them. sp_helptext is used internally to generate a CREATE script for the database object, an attempt is then made to change the CREATE to ALTER, there are instances where the change from CREATE to ALTER won't work. Sometimes sp_helptext incorrectly formats code on the database and when this happens the error will be repeated in the files generated by this stored procedure. You will need to rename the file ChopProcedures.vbs.txt to just ChopProcedures.vbs.