Home
About me and Nucleus
Partners
Links
Articles, tips etc.
My blog
Microsoft articles
Newsgroups
Course demo files
Sync logins
SMTP Alerter
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 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.
SQL Server Management Studio (DDL only) 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 cannot, however, select 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.
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.
http://www.microsoft.com/downloads/details.aspx?FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a&DisplayLang=en
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 DMO 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. The tool is free as of writing of this page.
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