Sean Price asks in my comments about the existance of SQL development tools that integrate with source control tools.
He also points out that you can use Visual Studio, I really don't like that way Visual Studio implements this as you still aren't getting a script generated for the objects you have developed. The best candidate I found a while back was Rapid SQL from Embarcadero. It generated scripts for all your procs, and checked them into VSS. Then when you did work, you worked inside the script and it deployed the code. It was all pretty seamless. It's not cheap though, would be fine if you have a big tools budget, but I'm an independant.
What does look promising is a tool that is being advertised on SQLTeam a lot right now called MSSQLExpress. I haven't tried it but it claims to integrate with VSS seamlessly. Maybe if someone has tried it they might like to post a comment here.
Being a web guy, until recently most of my work was classic ASP, and I use Macromedia's HomeSite for this. HomeSite is quite easy to script for so you can write an addin to run script files against a database. I kept all my proc files inside the HomeSite project and deployed them from there. Not perfect, but it does work. HomeSite will talk to most source control tools through the Microsoft SCC API. I plan to do something similar inside Visual Studio.NET but haven't gotten around to that one yet.
However, as I said in my previous post, I mainly use CVS now. What once was solely the realm of Unix coders is now really simple to set up on a Windows server thanks to the great work of Tony Hoyle at CVSNT. There is a tool available to integrate CVS with your IDE, it's called Igloo and it works pretty well. But my favourite CVS client (as I mentioned in the comments previously) is a tool called Tortoise CVS. It integrates into Windows Explorer giving you a right click context menu on all your version controlled directories. This is such a cool way to work, after you have made a batch of changes and are ready to commit them, right click in the directory in Explorer and you get a list of all the files underneath that folder to be checked in (yes, it does it recursively).
The main difference between working the VSS way and the CVS way, is that in VSS you check out a file to work on it, and that stops anyone else from touching it. In CVS, anyone can work on a file and it does its best to merge changes together on checkin (and lets you know if it's not sure). Vault (which is way cool) will work in either of these two ways as well.
Switching to CVS from VSS is a bit of a headshift, but I'm glad I made it. I don't even think about IDE integration anymore. This way makes so much more sense to me.
My name is Damian.... and I am a source control geek