SQL Script Generation

Update 2023: Check out our new documentation site for all of our various tools.

How many times have you needed to script out a table from a SQL Server database? What about all of the indexes? Or maybe it was just the procedures in a particular schema? Or export all of your data as insert/update statements?

Boy have we got the tool for you! Be sure to stick it out until the end, because I’m unveiling a hidden gem, but don’t skip ahead! ಠ╭╮ಠ

Truth is, the Vroom SQL Script Generator was an accidental creation. We here at Vroom Performance needed something that could automatically turn our development database into a production ready database deployment – that would include all the schema and data. The tool proved useful so we made it public. We’re cool like that.

The tool itself is pretty straight forward, so we’ll start with some screenshots:

You quite literally just put a check mark beside the objects you want to script, tell the tool what format you want the files and click GENERATE. You will then be free to enjoy your folder full of SQL scripts in whatever way floats your boat. ¯\_(ツ)_/¯

One thing to keep in mind is that the “Object Selection Mode” is just that. It allows you to check off the objects you want schema scripts for then switch over to “Export Data” and select the objects you want data scripts for. You can toggle back-and-forth between these modes but all of the selections will be used when you finally hit that generate button.

Automation!

If you dig into the program file folder “C:\Program Files\Vroom Performance Technologies\SQL Script Generator” on your PC, you’ll find the hidden gem: “SSGC Example Script.json”

This file is the automation script for the SQL Script Generator. Instead of running the UI, you can simply pass the SSGC.exe executable the path to an automation script and buddy are you off to the races!

Automation Example

Below is an actual excerpt from the Vroom Performance Suite automation script. We use this to generate the script that are used by our installer.

{
   //Connection information can be specified here and/or passed via command prompt.
   //Any values specified here will be overridden by the command line values.
   "ConnectionInfo": {
     "UseIntegratedSecurity": true,
     "DatabaseName": "VroomPS_Dev",
     "UseSSLConnection": false,
     "ApplicationName": "SQL Script Generator"
   },
 //Any options that are not specified will default to FALSE.
   //The value for "output" can be specified here or passed via command prompt.
   //Any values specified here will be overridden by the command line values.
   //Be sure to use two back-slashes when specifying an output path. e.g. "C:\Temp\Test.sql"
   //Note that "DestinationPath" can be a directory or a single file depending on the value of "ScriptToIndividualFiles".
   "DestinationOptions": {
     "DestinationPath": ".\_Server\Metabase\Database Scripts",
     "ScriptToIndividualFiles": true,
     "PrependSequentialNumber": true,
     "DeleteExistingFiles": true,
     "RollupTableArtifacts": true,
     "PrependSchemaName": true,
     "AppendObjectType": true
   },
 //Tells the engine whether it should consider scripting various types of objects.
   //Any options that are not specified will default to FALSE.
   "ScriptTypes": {
     "ScriptTables": true,
     "ScriptIndexes": true,
     "ScriptTriggers": true,
     "ScriptStatistics": true,
     "ScriptForeignKeys": true,
     "ScriptViews": true,
     "ScriptSchemas": true,
     "ScriptUserDefinedTypes": true,
     "ScriptXMLSchemaCollections": true,
     "ScriptUsers": false,
     "ScriptFunctions": true,
     "ScriptProcedures": true,
     "ScriptSequences": true,
     "ScriptCSharpModels": false,
     "ScriptData": true
   },
 //Various scripting options for SQL files.
   //Any options that are not specified will default to FALSE.
   "SQLScriptingOptions": {
     "IncludeIfNotExists": true,
     "DependencyHeuristics": false,
     "DetectDependencies": true,
     "IncludeSetOptions": true,
     "IncludeIndexOptions": true
   },
 //The "ScriptSchemaList" section specifies which database objects will have their schema scripts generated.
   //Alternately, if "ScriptSchemaExplicitExclude" is enabled then all database objects are scripted by default and the
   //  "ScriptSchemaList" section instead becomes an exclusion list objects which are to be excluded from being generated.
   //You can also use wildcards by placing a star/asterisk at the beginning, end or beginning AND end of any object below.
   "ScriptSchemaList": [ "*" ],
   "ScriptSchemaExplicitExclude": false,
 //The "ScriptDataList" section specifies which database tables will have their data scripts generated.
   //Alternately, if "ScriptDataExplicitExclude" is enabled then all database tables will have their data scripted by default
   //  and the "ScriptDataList" section instead becomes an exclusion list objects which are to be excluded from being generated.
   "ScriptDataList": [
     "dbo.AlertCategory",
     "dbo.AlertCondition",
     "dbo.AlertDefinition",
     …
     "dbo.Severity",
     "dbo.EntityAttributeName",
     "dbo.WindowsProcessFriendlyName"
   ],
   "ScriptDataExplicitExclude": false,
 //The "DoNotUpdateExistingDataList" section specifies which database tables will have their
   //  data scripts generated for insert only - meaning that they will not update existing data.
   //Alternately, if "DoNotUpdateExistingDataExplicitExclude" is enabled then all database tables will have this
   //  option by default and tables listed here will be exlcuded from this option
   "DoNotUpdateExistingDataList": [
     "dbo.ConfigurationEntry",
     …
     "dbo.AlertOperatorGroup"
   ],
   "DoNotUpdateExistingDataExplicitExclude": false
 }
 

Batch file for automation

Of course no good automation is completed without some batch/bash, so this is the batch file that we use to kick off the script generation within our build process.

@Echo Off

SET PATH=%PATH%;C:\Program Files\Vroom Performance Technologies\SQL Script Generator;

SSGC.exe /Config:_SchemaConfig.json

Pause

Ok, go script some databases. While you’re at it, keep us in mind. If you find something is missing, lacking or not as polished as you’d expect, then drop us an email. We enjoy honing these tools!