SQL Server 2000 provides a tool called scptxfr which generates DROP and CREATE scripts for all objects in a database.

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.19.4194.20191 (c) by 2008-2011

list and script databases and database objects.

usage: smoscript [options (leading '-' or '/')] [command]

connection options:

-s server server name
-d database database name
-u username username (default: integrated authentication)
-p password password (if -u is missing, password for sa)
-c connection connection string (sets -s, -d, -u, -p)

scripting options:

-o [schema.]object object name

-r generate DROP statements
-i include IF NOT EXISTS statements
-use generate USE [database]

-f filename output to file
-fa append to file if exists
-F directory output to directory

listing options:

-l[x]a list [in]accessible databases
-l[x]ro list databases [not] read-only
-l[x]sys list [non-] system databases
-ls list database status

dependency options:

-depth max. recursion depth
-dxt exclude tables in result

find options:

-fx string exclude this string literal (multi)
-fxs string exclude strings starting with this literal (multi)
-fi find string case-insensitive
-fc find string and display code

output options:

-A current ANSI codepage
-T Unicode


l list databases on server (implied by -s)
list objects in database (implied by -s -d)
s script all objects (implied by -s -d -F/-f)
script single object (implied by -s -d -o)
db list database properties
dep list object dependencies (-o)
fs find string literals
f string find string in modules

The SMOscript utility implements the following functions:

  • list all databases on a server
  • list all scriptable objects in a database
  • script CREATE or DROP statements for each object in database
  • find string literals used in TSQL code
  • find strings in TSQL modules
  • display object dependencies

Resulting scripts are either written to a single file, or to a separate file for each object. Table scripts will include child objects such as constraints and triggers.

Command line switches were selected to be compatible with scptxfr, but support both “-” and “/” as switch marker.

devioblog describes the latest changes to SMOscript here. SMOscript is available for download here.

Last Updated on Sunday, 26 June 2011 18:39
devio IT Services, Powered by Joomla! and designed by SiteGround web hosting