How do I add existing SQL Server stored procedures to source control?

Right now we have a bunch of datab开发者_JS百科ase servers with stored procedures running in them that are poorly documented and exist nowhere else.

When a change is made to one, there is no log, and it's really hard to figure out why something that was working suddenly fails.

We've recently switched to using proper version control using SVN, so I was hoping to add these stored procedures to version control.

We are a .Net shop, and I'm aware that there exists a Database project type. Would that be a good approach?

Alternatively I could just keep the stored procedures as text files and operate on those, but I'm wondering about the annoying deployment steps involved with doing so.

Check out redgate's sql source control. It has an interface built into SSMS and can integrate with SVN.

http://www.red-gate.com/products/sql-development/sql-source-control/

In Visual Studio, I created an empty project and added the scripts to it. Its part of my server schema solution. Now every single script in under source control. I also added a folder structure as well to try and keep the sanity. When the application is finished we will have probably close to 3000 scripts under source control under multiple server schemas. Not saying this is the best, but it is working for our project. The solution also has a utility which uses SMO to actually run/deploy all the scripts, so everything is contained in one solution. Screen shot attached showing some of the structure for your reference...

A database project is a good choice, in my opinion. You can import the entire database, including tables, views, stored procedures, etc. Visual Studio will use this information to build an in-memory model of the database.

It can use this for several purposes, including making sure your stored procedures are properly accessing the tables. For instance, it caught me trying to insert an Integer parameter into a smallint column.

Yes, I would recommend a database project -- you can synchronize database elements (tables/stored procedures, etc...) in either direction: source files to DB, or DB to source files.

You could start by creating an empty database project, then syncing from the existing database(s) to your project, which creates the .sql create scripts for you.

And you can use the database project to create deployment scripts too. Very handy.

We use the database project and have good results with it. All DML exists in the project (3000+ items).

Developers must make changes to the DML in source control and check them in, and only what is checked in will be promoted/deployed.

Our source control is TFS, and I noticed that deleting procs from the VS interface doesnt always mark the proc for deleting in source control. Not sure what SVN does with it.