Tibor Karaszi's SQL Server pages

Generate DDL scripts

Overview
Here you find some techniques and products that can generate DDL scripts for your database objects and/or INSERT statements for your data.
Thanks to SQL Server MVP Frank Kalis, this article is translated to German.

More information
I recommend that you treat DDL as source code. This typically mean that you keep your DDL in files and generally use a source control system with which you can check out and check in objects and which also can generate an earlier version of your database schema.
Having that said, lets dive into some of the alternatives, free utilities and products.

Explanations:

SMO means that the scripting utility uses the SMO programming interface which comes with the SQL Server 2005 and 2008 client tools.
DMO means that it uses the DMO programming interface which comes with SQL Server 2000 client tools.

I have not tested nor listed whether the tools supports versions older than SQL Server 2000. In most cases, "2005" also includes SQL Server 2008.

SQL Server Management Studio (DDL only, 2008 has options to include data) SMO, 2005 and 2000
Right-click a database and in the drop-down menu, select "Tasks", "Generate Scripts...". This will take you to a wizard where you select whether you want to generate script for all objects in a database or select objects. You can also customize a script in ways that weren't possible in Enterprise Manager (include COLLATE clause etc.). You need Management Studio 2005 sp2 or higher to create one file per object. You can also right-click on an object and select "Script [ObjectType] as...". Unfortunately, this will no allow you to define scripting options.

Powershell SMO, 2005 and 2000
This is just really an usage of SMO, from within a Powershell script. So, whatever you can do at the SMO level, you can do utilizing SMO from Powershell. Here's an example: http://blogs.msdn.com/buckwoody/archive/2009/07/02/powershell-and-sql-server-script-all-tables.aspx

Microsoft SQL Server Database Publishing Wizard (DDL and data) 2000 and 2005
This was designed for ease uploading a database to a hosting company without involving FTP and RESTORE or attach. The tool can generate a script including DDL and data. This can be saved as a .sql file or uploaded to your hosting company through a web service insterface. This is also available directly from Visual Studio 2008 (right-click the db...).
http://www.microsoft.com/downloads/details.aspx?FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a&DisplayLang=en
In SQL Server 2008, the publishing functionality has been incorporated into Management Studio (right-click database, Tasks, Publish using Web Service...).

Bill Graziano's Scriptio (DDL only) SMO, 2005
This allow you to create one file per object:
http://www.sqlteam.com/item.asp?ItemID=23185

Andrea Montanari's amScript and amInsert (DDL and data) SMO, 2005
These tools allow for scripting and also generating data:
http://www.asql.biz/en/Download2005.aspx

Nob Hill Software's Database Compare (DDL and data) 2005 and 2000
This package does, as the name implies, database comparsion with change script generation. Compare against an empty database and you get DDL and optionally DML:
http://www.nobhillsoft.com/nhdbcompare.aspx

The SMO API (DDL only) SMO, 2005 and 2000
The script generation code used by SQL Server Management Studio is available for us to use in the SMO API. This means that you can write your own applets to generate scripts, and have the script looking exactly as you want.
You can find some sample code for this at http://www.sqlteam.com/item.asp?ItemID=23185
You can also generate script using SMO per object. Just create an instance of the object and use the script method for that object.

SQL Scripter (data only) 2005 and 2000
This is a simple to use tool that can generate INSERT statements for data in an existing table, view or a query.
http://www.sqlscripter.com

xSQL Object (DDL and data) 2005 and 2000
Various tools for schema and data compare as well as for generating scripts for schema and/or data.
http://www.xsqlsoftware.com/

Enterprise Manager (DDL only) DMO, 2000
Right-click a database or an object and in the drop-down menu, select "All Tasks", "Generate SQL Scripts". EM can generate script for all objects in a database, including constraints, users etc. And you can save it all to one file or get one file per object. Unfortunately, you cannot customize the scripts. For instance, you might not want to have the COLLATE clause for all string columns. You often find yourself doing some search and replace after generating scripts in Enterprise Manager.

Query Analyzer (DDL only) DMO, 2000
In Query Analyzer (QA), Object Browser, you can right click an object and generate script. QA has the nice option that you can customize the script. To do this, select "Tools", "Options", "Script". However, QA will only generate script for one object at a time.

The SQLDMO API (DDL only) DMO, 2000
The script generation code used by Enterprise Manager and Query Analyzer is available for us to use in the SQLDMO API. This means that you can write your own applets to generate scripts, and have the script looking exactly as you want. Below you find a simple VB example which generates script for all objects in the database:


Dim oSS As SQLDMO.SQLServer

Dim oDb As SQLDMO.Database

Dim oT As SQLDMO.Transfer

Dim sS As String

Sub Script()

    Set oSS = New SQLDMO.SQLServer

    Set oT = New SQLDMO.Transfer

    oSS.Connect "server", "login", "password"  'Connect to the server

    Set oDb = oSS.Databases("pubs")           'Use a DB

    oT.CopyAllTables = True

    oDb.ScriptTransfer oT, SQLDMOXfrFile_SingleFile, "C:\pubs.sql"

End Sub

You can also generate script using DMO per object. Just create an instance of the object and use the script method for that object.

Scptxfr.exe (DDL only) DMO, 2000
This tool comes with SQL Server. See below URL for more information:
http://www.support.microsoft.com/?id=220163

QALite from Rac4sql (DDL and data) DMO, 2000
This free tool can generate scripts, including data. There are a lot of other features as well in QALite.
http://rac4sql.net/qalite_main.asp

ApexSQL Script from ApexSQL (data only) DMO, 2000
Currently this tool can generate INSERT statements for data, as of writing of this article there are plans for generating DDL as well. The tool has options for handling identity columns and the tool also handles all SQL Server datatypes.
http://www.apexsql.com

Scripting tools from Narayana Vyas Kondreddi (DDL and data)
Below you find a stored procedure which generates INSERT statements for a table as well as a COM component that can generate DDL. Both tools are free.
http://vyaskn.tripod.com/code.htm

Db Ghost from Innovartis (DDL and data)
Db Ghost Scripter, generates DDL and INSERT statements. The full Db Ghost product also does database schema comparison, data comparison and can also integrate with Visual SourceSafe.
http://www.innovartis.co.uk/Home.aspx