Version control is a critical aspect of software development, ensuring that changes to code are tracked, managed, and audited. However, database code is often not as well version controlled as normal application code. In this article, we will explore 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).
One common challenge in version controlling database code is the need to maintain different rights assigned to stored procedures. Dropping and recreating stored procedures can result in the loss of these rights. Therefore, our script must be able to create the procedure if it does not already exist, and alter it if it does.
To generate the scripts, we can leverage the sp_helptext stored procedure, which provides the CREATE source code for a routine. By calling this function from within a stored procedure, we can write a script that will generate the create/alter scripts required for version control.
Here are the steps to generate the script for the entire database’s routine source code:
- Install the provided stored procedure on the database you wish to document.
- Execute the stored procedure:
EXEC INFGenerateObjectScript '%',1,1,1 - Copy the contents of the messages tab in SQL Server Management Studio to the clipboard and save it to a text file in ANSI format.
- Use the provided vbscript file to split the text file into separate files, one for each routine.
- Copy the generated files into a directory and add them to your version control system.
If you only need to generate the script for an individual routine, you can execute the following command in SQL Server Management Studio:
EXEC INFGenerateObjectScript 'myProcedureName'
Copy and paste the script into a new text file and save it as an ANSI file. Add this file directly to your version control system.
By following these steps, you can automate the process of generating scripts for your SQL Server database routines and add them to your version control system. This allows for rigorous version control with features such as labelling, branching, merging, rolling-back, audit-trails, difference viewing, and authorization.
Disclaimer: The provided stored procedure and vbscript are provided “as is” without warranty of any kind. The method described in this article has been tested and used on SQL Server 2005, but it may work on other versions of SQL Server as well.
Hopefully, this article has provided you with a useful method for controlling your database source code alongside other related application source code. Happy version controlling!