blog.jj5.net (2003 to 2005)

Using SQL-DMO and JScript to script your SQL Server database

Sat Jan 22 23:59:00 UTC+1100 2005

Categories:

I love SQL-DMO.

I love JScript.

They're Russian! :)

Anyway, I just finished hacking out this script that generates a script of my database.

The goal is to have a set of scripts similar to this for setting up customer specific OLAP databases, and managing database version control in subversion, along with fully automated installations/updates (via nant) of the database schema on development, staging, and even production machines.

You can check it out if you're interested by running it from the command-line, like this:

cscript.exe //D script.js /server:(local) /database:northwind /file:test.sql

John.

--

/////////////////////////////////////////////////////////////////
// Command-Line Options
// ====================
//

  var COMMAND_LINE_OPTIONS = new Array(

    new CommandLineOption(
      "server",
      "ServerName",
      "Database server to query.",
      false,
      "(local)"
      ),
   
    new CommandLineOption(
      "database",
      "DatabaseName",
      "Name of database on server.",
      true
      ),
   
    new CommandLineOption(
      "user",
      "Username",
      "Database username, or blank for NTLM.",
      false,
      null
      ),
   
    new CommandLineOption(
      "pass",
      "Password",
      "Database account password, if not NTLM.",
      false,
      null
      ),

    new CommandLineOption(
      "file",
      "Filename",
      "The file to script the database to.",
      true
      ),

    new CommandLineOption(
      "debug",
      "IsDebuggable",
      "Can this script throw for JIT debugging?",
      false,
      false
      )

    );


/////////////////////////////////////////////////////////////////
// Constants
// =========
//

  var DEBUG = false;


/////////////////////////////////////////////////////////////////
// Class Definitions
// =================
//

  var SQLDMOScript = new SQLDMOScript();
  var SQLDMOScript2 = new SQLDMOScript2();
  var SQLDMOXfrFile = new SQLDMOXfrFile();

  var Environment = new Environment();
  var Debug = new Debug();

  var Console = WScript.StdOut;


/////////////////////////////////////////////////////////////////
// Entry Point
// ===========
//

  function Main( args ) {

    var serverName = args.ServerName;
    var databaseName = args.DatabaseName;
    var username = args.Username;
    var password = args.Password;
    var filename = args.Filename;

    var server = new ActiveXObject( "SQLDMO.SQLServer" );

    if ( username == null ) {

      server.Connect( serverName );

    }
    else {

      server.Connect( serverName, username, password );

    }

    var database = server.Databases.Item( databaseName, "dbo" );

    var scriptType = SQLDMOScript.Default ||
      SQLDMOScript.Indexes ||
      SQLDMOScript.ToFileOnly ||
      SQLDMOScript.IncludeHeaders ||
      SQLDMOScript.IncludeIfNotExists;

    var script2Type = SQLDMOScript2.Default;

    var xfrFile = SQLDMOXfrFile.SingleFile;

    var transfer = new ActiveXObject( "SQLDMO.Transfer2" );

    transfer.ScriptType = scriptType;
    transfer.Script2Type = script2Type;

    transfer.IncludeDB = true;
    transfer.IncludeDependencies = true;

    transfer.CopyAllFunctions = true;
    transfer.CopyAllObjects = true;

    database.ScriptTransfer(
      transfer,
      xfrFile,
      filename
      );

  }


/////////////////////////////////////////////////////////////////
// SQLDMO Script Enumeration
// =========================
//
// (an example of enumeration emulation :)
//
// When setting the ScriptType argument specifying multiple
// behaviors, combine values using an OR logical operator.
// Use these values to set ScriptType.
//
// see: Script Method ( SQL-DMO, Books Online )
//

  function SQLDMOScript() {

    // Generate Transact-SQL database privilege defining script.
    // Database permissions grant or deny  statement execution rights.
    this.DatabasePermissions = 32;

    // SQLDMOScript_PrimaryObject.
    this.Default = 4;

    // Generate Transact-SQL to remove referenced component.
    // Script tests for existence prior attempt to remove component.
    this.Drops = 1;

    // Generated script is prefixed with a header containing date
    // and time of generation and other descriptive information.
    this.IncludeHeaders = 131072;

    // Transact-SQL creating a component is prefixed by a check for
    // existence. When script is executed, component is created
    // only when a copy of the named component does not exist.
    this.IncludeIfNotExists = 4096;

    // SQLDMOScript_ClusteredIndexes,
    // SQLDMOScript_NonClusteredIndexes, and
    // SQLDMOScript_DRIIndexes combined using an OR logical operator.
    // Applies to both table and view objects.
    this.Indexes = 73736;

    // Individual Transact-SQL statements in the script are not
    // delimited using the connection-specific command terminator.
    // By default, individual Transact-SQL statements are delimited.
    this.NoCommandTerm = 32768;

    // Include Transact-SQL privilege defining statements when
    // scripting database objects.
    this.ObjectPermissions = 2;

    // Object names in Transact-SQL generated to remove an object are
    // qualified by the owner of the referenced object. Transact-SQL
    // generated to create the referenced object qualify the object
    // name using the current object owner.
    this.OwnerQualify = 262144;

    // SQLDMOScript_ObjectPermissions and
    // SQLDMOScript_DatabasePermissions combined using an OR logical
    // operator.
    this.Permissions = 34;

    // Generate Transact-SQL creating the referenced component.
    this.PrimaryObject = 4;

    // When scripting object creation for a table or user-defined
    // data type, convert specification of timestamp data type to
    // binary(8).
    this.TimestampToBinary = 524288;

    // Most SQL-DMO object scripting methods specify both a return
    // value and an optional output file. When used, and an output
    // file is specified, the method does not return the script to
    // the caller, but only writes the script to the output file.
    this.ToFileOnly = 64;

    // Use quote characters to delimit identifier parts when
    // scripting object names.
    this.UseQuotedIdentifiers = -1;

  }


/////////////////////////////////////////////////////////////////
// SQLDMO Script2 Enumeration
// ==========================
//
// When setting the Script2Type argument specifying multiple
// behaviors, combine values using an OR logical operator.
// Use these values to set Script2Type.
//
// see: Script Method ( SQL-DMO, Books Online )
//

  function SQLDMOScript2() {

    // Disable features available in instances of SQL Server 2000
    // so that output is compatible with an instance of SQL Server
    // version 7.0. Disabled features are:
    // Column-level collation
    // User-defined functions
    // Extended properties
    // Instead of triggers on tables and views
    // Indexes on views
    // Indexes on computed columns
    // Descending indexes
    // Default is OFF
    this.SevenOnly = 16777216;  // 70Only

    // Generate Transact-SQL script creating SQLServerAgent
    // service jobs and alerts.
    this.AgentAlertJob = 2048;

    // When scripting an alert, generate script creating
    // notifications for the alert.
    this.AgentNotify = 1024;

    // Generated script file uses multibyte characters.
    // Code page 1252 is used to determine character meaning.
    this.AnsiFile = 2;

    // Generate Transact-SQL SET ANSI_PADDING ON and
    // SET ANSI_PADDDING OFF statements before and after
    // CREATE TABLE statements in the generated script.
    // Applies only when scripting references a SQL Server table.
    this.AnsiPadding = 1;

    // No scripting options specified.
    this.Default = 0;

    // Encrypt passwords with script. When specified,
    // SQLDMOScript2_UnicodeFile must be specified as well.
    this.EncryptPWD = 128;

    // Ignore all SQLDMO_SCRIPT_TYPE settings. Use to script
    // extended property settings only. Script may require
    // editing prior to running on destination database.
    this.ExtendedOnly = 67108864;

    // Include extended property scripting as part of object
    // scripting.
    this.ExtendedProperty = 4194304;

    // Command batch includes Transact-SQL statements creating
    // Microsoft Search full-text catalogs.
    this.FullTextCat = 2097152;

    // Generated script includes statements defining Microsoft
    // Search full-text indexing. Applies only when scripting
    // references a SQL Server table. Include security
    // identifiers for logons scripted.
    this.FullTextIndex = 524288;

    // Disable the job at the end of script creation.
    // SQLDMOScript2_PrimaryObject must also be specified.
    this.JobDisable = 33554432;

    // Include security identifiers for logins scripted.
    this.LoginSID = 8192;

    // Generated script creates replication implementing
    // triggers as system objects. Applies only when
    // scripting replication articles.
    this.MarkTriggers = 32;
   
    // Do not script the collation clause if source is later
    // tha SQL Server version 7.0. The default is to generate
    // collation.
    this.NoCollation = 8388608;

    // Generated script does not include 'ON '
    // clause directing filegroup use. Applies only when
    // scripting references a SQL Server table.
    this.NoFG = 16;

    // Do not script hypothetical indexes used to implement
    // the CREATE STATISTICS statement. Applies only when
    // scripting references a SQL Server table.
    this.NoWhatIfIndexes = 512;

    // Generated script output file is a Unicode-character text
    // file.
    this.UnicodeFile = 4;

  }


/////////////////////////////////////////////////////////////////
// SQLDMO SQLDMOXfrFile Enumeration
// ================================
//
// Setting the ScriptFileMode argument affects interpretation
// of the ScriptFile argument. When setting ScriptFileMode,
// use these values, setting ScriptFile as described.
//
// see: SQL Server Books Online, SQL-DMO, ScriptTransfer
//

  function SQLDMOXfrFile() {

    // SQLDMOXfrFile_SummaryFiles.
    this.Default = 1;

    // Command batch is written to one file. Specify the file
    // name using the ScriptFile argument. If a path is not
    // included in the file name, the file is created in the
    // directory indicated by the client computer environment
    // variable TEMP.
    this.SingleFile = 2;

    // Command batch is written to multiple files, one file
    // for each SQL Server component transferred. Specify a
    // path using the ScriptFile argument. If a path is not
    // specified, the files are created in the directory
    // indicated by the client computer environment variable
    // TEMP.
    this.SingleFilePerObject = 4;

    // Command batch is written to one file. Command batch
    // contents are organized by object type. Specify the
    // file name using the ScriptFile argument. If a path
    // is not included in the file name, the file is created
    // in the directory indicated by the client computer
    // environment variable TEMP.
    this.SingleSummaryFile = 8;

    // Command batch is written to multiple files, one file
    // for each kind of object transferred. For example,
    // generate a file for user-defined data types and a
    // separate file for tables. Specify a path using the
    // ScriptFile argument. If a path is not specified, the
    // files are created in the directory indicated by the
    // client computer environment variable TEMP.
    this.SummaryFiles = 1;

  }


/////////////////////////////////////////////////////////////////
// Environment
// ===========
//

  //
  // Constructor
  //
  function Environment() {

    this.GetCommandLineArguments = Environment_GetCommandLineArguments;

  }

  //
  // Method Definitions
  //
  function Environment_GetCommandLineArguments() {


    var args = WScript.Arguments.Named;

    var result = new Array();

    for ( i in COMMAND_LINE_OPTIONS ) {

      var option = COMMAND_LINE_OPTIONS[ i ];
      var value = args.Item( option.CommandLineName );
      var hasValue = args.Exists( option.CommandLineName );

      if ( option.IsRequired && ! hasValue ) {

        throw "Command-line exception.";

      }
      else if ( option.IsRequired || hasValue ) {

        result[ option.PropertyName ] = value;

      }
      else {

        result[ option.PropertyName ] = option.DefaultValue;

      }
    }

    return result;

  }

  //
  // Private Types
  //
  function CommandLineOption(
    commandLineName,
    propertyName,
    description,
    isRequired,
    defaultValue
    ) {

    this.CommandLineName = commandLineName;
    this.PropertyName = propertyName;
    this.Description = description;
    this.IsRequired = isRequired;
    this.DefaultValue = defaultValue;

  }


/////////////////////////////////////////////////////////////////
// Debug
// =====
//

  //
  // Constructor
  //
  function Debug() {

    this.Assert = Debug_Assert;

  }

  //
  // Method Definitions
  //
  function Debug_Assert( condition, message ) {

    if ( ! condition ) {

      var e = "Assertion violated: " + message;

      Console.WriteLine( e );

      if ( DEBUG ) {

        throw e;

      }
      else {

        WScript.Quit();

      }
    }
  }


/////////////////////////////////////////////////////////////////
// Bootstrap
// =========
//

  Bootstrap();

  function Bootstrap() {

    var args;
   
    try {

      args = Environment.GetCommandLineArguments();

    }
    catch ( ex ) {}

    if ( args == null ) {

      try {

        ShowUsage();

      }
      catch ( ex ) {}

      return;

    }

    // set debug level
    try {

      if ( args.IsDebuggable == true ) {

        DEBUG = true;

      }
    }
    catch ( ex ) {}

    if ( DEBUG ) {

      // call entry point
      Main( args );

    }
    else {
    
      try {

        Main( args );

      }
      catch ( ex ) {

        try {

          Console.WriteLine( "Script failed. " + ex );

        }
        catch ( ex ) {}

      }
    }
  }

  function ShowUsage() {

    Console.WriteLine( "TODO: ShowUsage." );

  }

Copyright © 2003-2005 John Elliot