Source Control and Stored Procedures

Question

I was wondering how to put all stored procedures in SQL 2000 under source control.
We use Subversion for all of our regular source code, so it would be great if there was a solution to the problem with Subversion.

Do you have any ideas?

Update 16-02-2009: This is the vbs script I used to export all stored procedures:

Set con = CreateObject("ADODB.Connection") con.ConnectionString = "*** Database connection string here ***" con.Open Set rs = CreateObject("ADODB.RecordSet") rs.ActiveConnection = con strSQL = "SELECT ROUTINE_NAME, ROUTINE_DEFINITION " & _ "FROM INFORMATION_SCHEMA.routines " & _ "WHERE ROUTINE_NAME NOT LIKE 'dt_%' " & _ "ORDER BY 1" Set fso = CreateObject("Scripting.FileSystemObject") rs.Open strSQL While Not rs.Eof filename = rs("ROUTINE_NAME") & ".sql" routineSQL = rs("ROUTINE_DEFINITION") Set tf = fso.CreateTextFile(filename, True) tf.Write routineSQL tf.Close set tf = Nothing rs.MoveNext Wend Set fso = Nothing rs.Close Set rs = Nothing 

Answer 1

As other people have said, start with each saved proc in a separate text file under source control. Write a script that will delete all stored procedures and then re-create them from text files (when registering / reporting any errors) - this script should be easy to run. Then every time you update the source code, restart the script. All changes to stored procedures should be performed in a text file, and not in a "live" copy in the local database, otherwise you will lose the changes when updating.

You will soon want to audit your database schema and create upgrade scripts, etc.

If you are using a SQL server only, consider SQL Compare from Reg-Gate . I think that it will compare the stored procs (and other sql) in the text file with what is in your database and synchronize them. Thus, you can use the editing tools in SqlServer to edit live stored procedures.

(As of the end of 2009, Red-Gate is about to send Sql Compare to Oracle )

I was told that ApexSQL Diff is another option instead of Sql Compare, ApexSQL Edit claims to provide version control integration.

In the high-end, consider the Visual Studio Team System Database Database Edition, however it is very expensive, then you may have to pay even more for third-party support for Oracle. But if you are a Microsoft partner (or you can become one of them), you can get some help very cheaply.

See also You control the source data of your databases in StackOverflow to get a good answer to a big problem.

Answer 2

You typically track changes to SQL scripts in the source control. For example, you have a checkin for your base schema for your database. Then you add new SQL files to modify your schema. This way you can deploy the exact version for testing. Then you can use build automation to automatically test some of your scripts by running them with test databases with the actual data in them.

There are many database comparison tools that can help you understand what has changed between versions.

Answer 3

Script all stored procedures in a folder. One stored procedure file.

Then just place this folder full of files under the source code, just like for your other source code.

It also helps if there is a batch file or similarly add these stored procedures together, this will be your "updated database to the latest version" script.

There are ways to manage stored procedures in the database itself, but I found this to be the easiest method.

Answer 4

In addition to Red Compare SQL Compare, we’ll look at the ApexSQL Diff tool for checking structural differences between databases. You can also consider management tools that integrate source control. ApexSQL Edit provides version control integration.

Answer 5

See Josef's solution here: What is the best way to version control my SQL server stored procedures?

It has a tool to automate the creation of script files, which can then be checked on SVN (or on any other repository, really).