sql server - Stored procedures/DB schema in source control

Question

Do you guys keep track of stored procedures and database schema in your source control system of choice?

When you make a change (add a table, update an stored proc, how do you get the changes into source control?

We use SQL Server at work, and I've begun using darcs for versioning, but I'd be curious about general strategies as well as any handy tools.

Edit: Wow, thanks for all the great suggestions, guys! I wish I could select more than one "Accepted Answer"!

Answer 1

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 #1234
ALTER TABLE asdf ADD COLUMN MyNewID INT
GO

-- change #5678
ALTER TABLE asdf DROP COLUMN SomeOtherID
GO

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]
GO

CREATE PROCEDURE [usp_MyProc]
(
    @UserID INT
)
AS

SET NOCOUNT ON

-- stored procedure logic.

SET NOCOUNT OFF

GO  

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