Now that we're using SQL Server 2005 as our "default" database (while, of course, maintaining SQL Server 2000 compatibility), we can start looking into the new capabilities Microsoft has provided. One of these capabilities are the new SQL Server Management Objects (SMO), which are a .Net-based management API for SQL Server. There's an interesting post about scripting SQL objects over on SQLTeam.com that highlights some of the basic capabilities.

One of the places I think this could be a great fit for us is automatically scripting database objects, for instance as part of the nightly build, in order to provide two things:
1. Automatic updating of the database creation statements in source control. Developers could make a change in the development database and the next day source control would automatically pick up the changes.
2. Diff'ing database versions. This will be more useful for iMIS 15 in the near term, but Freedom will benefit from it as well once versioning and updates become more a part of the process. Diff'ing an entire database would allow us to easily identify changes needed as part of an upgrade process, to the point of possibly even automating part or all of the change script creation (rather than depending on developers remembering exactly which changes were made and reproducing them correctly in the scripts).

SMO does work with SQL Server 2000 if you set an option correctly (set TargetServerVersion to SqlServerVersion.Version80 instead of the default SqlServerVersion.Version90), so this tool should be able to span maintenance work as well.