Script SQL Server DDL

Scripting SQL Server DDL
Richard Sutherland

If you buy into the theory that all database objects should be contained in a source management system such as Visual SourceSafe, and that deployment of database projects should be done from the source management system, then the manner in which Microsoft's Visual Studio 2005 Team Edition for Database Professionals [a.k.a., the "Data Dude"] should be of interest to you. It provides a tool which, among other things, will script out every object in a database to individual files -- something SQL Server Management Studio is incapable of performing. These files can then be checked into source management, and from that point forward all database development would be done in a normal development check-out/modify/check-in fashion to which many application developers are accustomed.

The Data Dude contains many features beyond scripting, but if you are just seeking a way to script out databases, and if you consider the expense the Data Dude for this process to be extreme, then a free open source project may be your answer. A “Script SQL Database DDL” tool can be found at: http://sourceforge.net/projects/script-sql-db. It contains a downloadable version of ScriptDB.exe, as well as the source code used to develop it in a Visual Studio project.

This tool scripts out entire databases into a directory tree which mimics the structure produced by the Data Dude. It produces a separate file for every object. In the case of tables, its triggers, indexes, constraints and keys each get there own file [just as the Data Dude does]. This differs from SQL Server Management Studio which, when scripting a table, includes all of these objects in the same file as the CREATE TABLE statement -- and this makes it difficult to use such a file in a source management system.

Tables are the oddball item when it comes to using a source management system, but if they are handled as individual components it makes much more sen