I was reading secretGeeks blog entry titled "11 Tools for Database Versioning" and it reminded of some work I did and blogged about quite a while back.
The approach we took then was to have a database project in our solution. The project included schema upgrade scripts along with triggers and stored procs. Each object had it's own file with a matching filename for clarity. In addition, each script needed to be capable of running >1 on the same database and each object needed to be capable of uninstalling (if necessary) and reinstalling.
Once all of the objects were in place, I created an XML control file in the project that describes the objects to be included and the order of install. The following links describe the process (first link has minimal source code).
Using an XML Control File for Installation Order
Building a database script using NAnt - supporting c# source code
Avoiding Trace during Sql Script installing (w/ source)
Trace detection above is performed in Sql 2000. There are some changes with how the mechanism works for Sql2005. Check out: http://sqljunkies.com/WebLog/mz1313/archive/2006/05/09/20962.aspx (Thanks to Paul Czywczynski for the link!)