Source Control and stored procedures

Question

I have been wondering how to put all the stored procedures on a SQL 2000 under source control.
We are using Subversion for all our normal source code, so it would be great if there were a solution to the problem using Subversion.

Do you have any ideas?

Update 16-02-2009: This is the vbs script i used to export all the 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

score 4 · Answer 1 · answered Jan 22 '09 at 11:15

Usually you track the changes to SQL scripts in source control. For example, you have a checkin for your base schema for your database. Then you keep adding new SQL files for changes to your schema. That way you can deploy to an exact version for testing purposes. Then you can use build automation to automatically test some of your scripts by executing them against test databases with actual data in them.

There are lots of database diff tools around that can help you work out what's changed between versions.

score 3 · Answer 2 · answered Jan 22 '09 at 11:29

Script all of the stored procedures into a folder. One file per stored procedure.

Then simply put that folder full of files under source control, exactly as you would for your other source code.

It also helps if there is a batch file or similar to append these stored procedures together, this will be your "upgrade database to latest version" script.

There are ways of managing the stored procedures within the database itself, but I have found this to be the simplest method.

score 3 · Accepted Answer · edited May 23 '17 at 12:01

As other people have said, start off with each stored proc in a separated text file that is under source control. Write a script that deletes all you stored procedures then re-creates them from the text files (while logging/reporting any errors) – this script should be easy to run. Then every time you update from source control rerun the script. All edits to stored procedures should be done to the text file, not the “live” copy on your local database otherwise you will loose changes when you do a update.

You will soon want someway of auditing your database schema and creating upgrade scripts etc.

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

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

I have been told that ApexSQL's Diff tool is another option instead of Sql Compare, ApexSQL's Edit claims to provide source control integration.

At the high-end consider Visual Studio Team System Database Edition, however it costs a lot, then you may have to pay even more for Oracle support from a 3rd party. But if you are a Microsoft partner (or can become one) you may get some copes very cheaply.

See also Do you source control your databases? on StackOverflow for a good set of answers on the bigger problem.

score 2 · Answer 4 · answered Jan 22 '09 at 21:10

In addition to Red Gate's SQL Compare, consider ApexSQL's Diff tool for checking for structure differences between databases. You may also want to consider management tools that integrate source control. ApexSQL's Edit provides source control integration.

Source Control and stored procedures

4 Answers4

Linked