Saving a saved procedure

Question

How do you manage revisions of stored procedures?

We have a BI solution on SQL Server 2005 with hundreds of stored procedures. What would be a good way to get them in Subversion? What are the recommended tools for script stored procedures for files?

Answer 1

Undoubtedly, there are a lot of ready-made products that you can buy (I think several RedGate tools can come in handy here), as well as Visual Studio Team Suite - Database Edition.

In the light of buying something, why not consider using SQL Management Objects (SMOs)?

I have written several utilities that generate T-SQL scripts (using the Scripter class), which creates the same scripts that you get from scripting through SQL Server Management Studio (it uses the same functions).

You can integrate such a utility into the script / build build process, which will allow you to create scripts and then the version and check them in the original repository. In addition, you can load scripts into a single file (if desired), which is superior to hundreds of individual files.

I wrote a note about this approach a while ago.

Read more about SMO Class Scriptor

Here are some more articles that might be helpful:

http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated

http://sqlblog.com/blogs/ben_miller/archive/2007/10/03/table-scripting-with-smo-part-1.aspx

Answer 2

See here and here for a start.

Answer 3

Please read here. What is the best way to version control SQL Server stored procedures? . I can help you identify a couple of solutions to this problem.

Answer 4

Earlier, I used the Visual Studio database project to control the creation of table scripts, stored procedure scripts, etc. I am sure you could use subversion to manage these files just like any Visual Studio project.

I used the built-in functions for procs scripts, but I'm sure Redgate will have some useful tools for this.