Stored procedures/DB schema in source control

We choose to script everything, and that includes all stored procedures and schema changes. No wysiwyg tools, and no fancy 'sync' programs are necessary.

Schema changes are easy, all you need to do is create and maintain a single file for that version, including all schema and data changes. This becomes your conversion script from version x to x+1. You can then run it against a production backup and integrate that into your 'daily build' to verify that it works without errors. Note it's important not to change or delete already written schema / data loading sql as you can end up breaking any sql written later.

-- change #1234ALTER TABLE asdf ADD COLUMN MyNewID INTGO-- change #5678ALTER TABLE asdf DROP COLUMN SomeOtherIDGO

For stored procedures, we elect for a single file per sproc, and it uses the drop/create form. All stored procedures are recreated at deployment. The downside is that if a change was done outside source control, the change is lost. At the same time, that's true for any code, but your DBA'a need to be aware of this. This really stops people outside the team mucking with your stored procedures, as their changes are lost in an upgrade.

Using Sql Server, the syntax looks like this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_MyProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [usp_MyProc]GOCREATE PROCEDURE [usp_MyProc](    @UserID INT)ASSET NOCOUNT ON-- stored procedure logic.SET NOCOUNT OFFGO  

The only thing left to do is write a utility program that collates all the individual files and creates a new file with the entire set of updates (as a single script). Do this by first adding the schema changes then recursing the directory structure and including all the stored procedure files.

As an upside to scripting everything, you'll become much better at reading and writing SQL. You can also make this entire process more elaborate, but this is the basic format of how to source-control all sql without any special software.

addendum: Rick is correct that you will lose permissions on stored procedures with DROP/CREATE, so you may need to write another script will re-enable specific permissions. This permission script would be the last to run. Our experience found more issues with ALTER verses DROP/CREATE semantics. YMMV

create a "Database project" in Visual Studio to write and manage your sQL code and keep the project under version control together with the rest of your solution.

The solution we used at my last job was to number the scripts as they were added to source control:

01.CreateUserTable.sql
02.PopulateUserTable
03.AlterUserTable.sql
04.CreateOrderTable.sql

The idea was that we always knew which order to run the scripts, and we could avoid having to manage data integrity issues that might arise if you tried modifying script #1 (which would presumable cause the INSERTs in #2 to fail).