SQL Server 2000 provides a tool called scptxfr which generates DROP and CREATE scripts for all objects in a database. Unfortunately it is not included in SQL Server 2005.
In the batch files I described in my posts about automating the build process, I also call it to generate a script for all database objects which is then used in version control.
Since it is not included in the latest SQL Server versions, I developed a replacement for scptxfr which uses the .Net SMO libraries for script generation called SMOscript:
smoscript 0.10.3169.17676 (c) by devio.at 2008 list and script databases and database objects. usage: smoscript [options] [command] options: (leading '-' or '/') -s server server name -d database database name -u username username (default: integrated authentication) -p password password (if -u is missing, password for sa) -r generate DROP statements -i include IF NOT EXISTS statements -f filename output to file -F directory output to directory -A current ANSI codepage -O ASCII -T Unicode -U UTF8 commands: l list s script object/s list databases on server (implied by -s) list objects in database (implied by -s -d) script object (implied by -s -d -o) script all objects (implied by -s -d -F/-f)
The SMOscript utility implements the following functions:
- list all databases on a server
- list all objects in a database
- script CREATE or DROP statements for each object in database
Resulting script are either written to a single file, or to a separate file for each object.
Command line switches were selected to be compatible with scptxfr, but support both “-” and “/” as switch marker.
SMOscript is available for download here.
Pingback: SMOscript: Scripting database objects using SMO « devioblog
Best tool ever! Thank you very much.
I there a place I can get the source code?
This does NOT work for the latest Azure SQL and Azure SQL managed instance! Please help! Thanks!