jamesearnshaw.com

mssql-scripter Examples

by James Earnshaw on July 22, 2023

mssql-scripter is a command line tool for scripting SQL Server objects. It aims to match the functionality of the scripting wizard in SQL Server Management Studio (SSMS). With it you can script and save database objects to files on disk. From there you could put everything in source control, or make a build script. Next you could automate builds and tests. Then you might decide to explore DevOps practices for SQL Server.

Getting started

mssql-scripter is written in Python so make sure Python is installed. Then create a directory, a Python virtual environment, activate it, and use pip to install mssql-scripter:

# Create directory
cd C:\work
mkdir mssql-scripter-examples
cd .\mssql-scripter-examples

# Create virtual environment and activate it
python -m venv venv
.\venv\Scripts\activate

# Install with pip
pip install mssql-scripter

API

mssql-scripter is a command line tool so typically you'd run it from a shell program like PowerShell. Parameters specify the desired object(s) to script. This gives you significant control over which objects (e.g. tables only) to script and how (e.g. include DROP statements) to script them.

Calling mssql-scrtiper feels a bit like calling SQLCMD (e.g. -S for server, -d for database). Here's a simple example:

mssql-scripter -S localhost -d AdventureWorks2017 -U username -P password `
-f .\scripted-tables.sql --include-types Table

This tells mssql-scripter to script only the tables from the AdventureWorks2017 database.

These options were used:

  • S is the server
  • d is the database
  • U is the username
  • P is the password
  • f is the file path
  • --include-types accepts a list (space separated) of object types to include

You can get help with:

mssql-scripter -h

The following examples don't use the username and password parameters because SQL Server was installed locally on a dev box.

Example 1

I like to script objects to individual files. One reason is that it's easier, from a source control perspective, to manage an object in its own file than multiple objects in one file. This behaviour is specified with --file-per-object.

mssql-scripter -S localhost -d AdventureWorks2017 -f .\ --file-per-object

Notice how the file path parameter -f (.\) is not a filename like .\foo.sql. When --file-per-object is used the f parameter is treated as a directory not a file. \. means save the files to the current location.

The result is that each object is written to its own file: file-per-object

You can count the number of each object with:

$objects = New-Object System.Collections.Generic.List[System.Object]
dir | ForEach-Object {
    $name = $_.Name
    $nameWithoutExtension = [IO.Path]::GetFileNameWithoutExtension($name)
    $arr = $nameWithoutExtension.split('.')
    $objectType = $arr[$arr.Count-1]
    $objects.Add($objectType)
}
$objects | Group-Object | Select-Object Name, Count

output:

Name                Count
----                -----
Database                1
FullTextCatalog         1
UserDefinedDataType     6
Table                  71
StoredProcedure        11
UserDefinedFunction    11
DdlTrigger              1
XmlSchemaCollection     6
Schema                  5
View                   20

Example 2

Stored procedures are scripted with CREATE PROCEDURE by default, however, these days I prefer the CREATE OR ALTER syntax. Unfortunately I couldn't find a parameter to specify CREATE OR ALTER (but there is --check-for-existence to conditionally drop an object if it exists) so my work around is to replace CREATE PROCEDURE with CREATE OR ALTER PROCEDURE in each file. This replacement is done by the following PowerShell snippet:

# Script procedures files
mssql-scripter -S localhost -d AdventureWorks2017 -f .\ `
--include-types StoredProcedure --file-per-object

# Replace in each file CREATE PROCEDURE with CREATE OR ALTER PROCEDURE
$matches = Get-ChildItem *StoredProcedure.sql | `
Select-String -Pattern 'CREATE\sPROCEDURE'

$matches.Filename | Foreach-Object {
    $content = Get-Content $_ -Raw
    ($content -replace 'CREATE\sPROCEDURE', 'CREATE OR ALTER PROCEDURE') | `
    Set-Content -Path $_
}

Alternatively you can include the DROP PROCEDURE statement with --script-drop-create but that can lead to errors if the object doesn't already exist.

Example 3

This example scripts the tables for multiple databases and saves the files to a directory for each database. The databases are stored in the variable $database which is an array that gets piped to the Foreach-Object cmdlet. Notice the use of Push-Location and Pop-Location which I've used to return to the location that the script was at before it started:

$server = 'localhost'
$database = ('AdventureWorks2017','WideWorldImporters')
$database | ForEach-Object {
        Push-Location

        # Create a directory for the database
        if(-not (Test-Path $_)){
                mkdir $_
        }
        cd $_

        # Remove any files from before
        rm *.sql

        mssql-scripter.bat -S $server `
        -d $_ `
        -f .\ `
        --file-per-object `
        --script-drop-create `
        --include-types Table

        Pop-Location
}

msqsql-scripter gets called twice, once for each database.

Example 4

This example takes things further by creating a folder for each object type and then saving each object to the folder matching its type.

Set-Location C:\work\mssql-scripter
$server = 'localhost'
$databases = ('AdventureWorks2017','WideWorldImporters')
$objects = ('Table','StoredProcedure','View', `
'FullTextCatalog','Schema','XmlSchemaCollection', `
'UserDefinedFunction','DdlTrigger','UserDefinedDataType')
$databases | ForEach-Object {
        $database = $_
        # Create a directory for the database
        if(-not (Test-Path $database)){
            mkdir $database
        }
        Push-Location
        Set-Location $database

        $objects | ForEach-Object {
            $object = $_
            if(-not (Test-Path $object)){
                mkdir $object
            }
            Push-Location
            Set-Location $object
            rm *.sql
            mssql-scripter.bat -S $server `
            -d $database `
            -f .\ `
            --file-per-object `
            --script-drop-create `
            --include-types $object
            Pop-Location
        }
        Pop-Location
}

Example 5

mssql-scripter can also script data. This is useful when you have a table storing a small amount of static reference data that doesn't change very often, e.g. a date dimension. I wouldn't do this on a big fact table. To script data use the --data-only parameter. AdventureWorks has a candidate table that you might script the data for: Sales.SalesReason.

mssql-scripter.bat -S localhost `
        -d AdventureWorks2017 -f .\Load.Sales.SalesReason.sql `
        --include-objects Sales.SalesReason `
        --data-only

The output is a file called Load.Sales.SalesReason.sql containing the data scripted as INSERT statements into the Sales.SalesReason table. The DDL (i.e. CREATE TABLE) is not included when using --data-only.

Conclusion

mssql-scripter is a powerful command line tool for scripting SQL Server databases. This post showed a few examples to get started but the real value comes when using it, along with other tools, like source control, to assist in the task of database development.