Do everyone keep an eye on saved methods and database schema inside your source control system of preference?

Whenever you create a change (give a table, update an saved proc, how can you obtain the changes into source control?

We use SQL Server at the office, and I have begun using darcs for versioning, but I'd be interested in general methods in addition to any handy tools.

Edit: Wow, thanks for the great suggestions, men! If only I possibly could choose several "Recognized Answer"!

We decide to script everything, which includes all saved methods and schema changes. No wysiwyg tools, with no fancy 'sync' programs are essential.

Schema changes are easy, all that you should do is create and keep just one file for your version, including all schema and data changes. This becomes your conversion script from version x to x+1. After that you can run it against a production backup and integrate that to your 'daily build' to ensure it works without errors. Note it is important to not change or remove already written schema / data loading sql as possible finish 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 saved methods, we elect for any single file per sproc, also it uses the drop/create form. All saved methods are recreated at deployment. However when a big change ended outdoors source control, the modification sheds. Simultaneously, that's true for just about any code, however your DBA'a need to understand this. This really stops people outdoors they mucking together with your saved methods, his or her changes are lost within an upgrade.

Using Sql Server, the syntax appears like this:

if is available (choose * 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

-- saved procedure logic.

SET NOCOUNT OFF

GO

The only real factor left to complete is write a software application program that collates all of the individual files and produces a brand new file using the entire group of updates (like a single script). Do that beginning with adding the schema changes then recursing your directory structure and including all of the saved procedure files.

Being an upside to scripting everything, you'll become far better at reading through and writing SQL. You may also get this to entire process more elaborate, but this is actually the fundamental format of methods to source-control all sql with no special software.

addendum: Ron is correct that you'll lose permissions on saved methods with DROP/CREATE, so you may want to write another script will re-enable specific permissions. This permission script will be the last to operate. Our experience found more difficulties with ALTER verses DROP/CREATE semantics. YMMV

The answer we used inside my last job ended up being to number the scripts because they were put into source control:

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

The concept was that people always understood which order to operate the scripts, and that we could avoid needing to manage data integrity problems that might arise should you attempted modifying script #1 (which may presumable make the Card inserts in #2 to fail).

produce a "Database project" in Visual Studio to create and manage your sQL code and the project under version control with the relaxation of the solution.