Source Control and stored procedures

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

See Josef's solution here: http://stackoverflow.com/questions/146543/what-is-the-best-way-to-version-control-my-sql-server-stored-procedures

He has a tool for automating the creation of the script files, which can then be checked in to SVN (or any other repository, really).