Mssql-scripter, a new SQL Server command-line tool that generates T-SQL scripts

Here you will find a new SQL Server command-line tool for generating T-SQL scripts: Mssql-scripter. It supports generating create and insert T-SQL scripts for databases in SQL Server, Azure SQL db, and Azure SQL DW.

Mssql-scripter is a cross-platform command-line tool that is functionally equivalent to the Generate and Publish Scripts Wizard in SQL Server Management Studio.

We can use it on Linux, MacOS, and windows to generate data definition languages (ddl-data definition Language) and data manipulation languages (Dml–data manipulation Language), And the generated T-SQL scripts can run on all platforms of SQL Server, Azure SQL Database, and Azure SQL Data warehouse.

Installation1. Windows

A) Install Python, the latest installation package: https://www.python.org/downloads/, note the option to select "Add python to PATH" when installing:

b) Install Mssql-scripter, execute the following command in the command line:

Pip Install Mssql-scripter

2. Linux

A) Check the PIP version, whether it is 9.0 and above:

Pip–version

b) If the PIP is not installed or the version is less than 9.0, use the following command to install and upgrade the version:

sudo apt-get install python-pipsudo pip install--upgrade pip

c) Install Mssql-scripter:

sudo pip install Mssql-scripter

If the system is Ubuntu or Debian, you need to install the Libunwind8 package:

Ubuntu & 17

Execute the following command:

sudo apt-get updatesudo apt-get install Libunwind8

Debian 8 (temporarily no environment, not tested)

File '/etc/apt/sources.list ' needs to be updated:

Deb Http://ftp.us.debian.org/debian/jessie Main

Execute the following command:

sudo apt-get updatesudo apt-get install Libunwind8

3. MacOS (temporarily no environment, not tested)

A) Check the PIP version, whether it is 9.0 and above:

Pip–version

b) If the PIP is not installed or the version is less than 9.0, use the following command to install and upgrade the version:

sudo apt-get install python-pipsudo pip install--upgrade pip

c) Install Mssql-scripter:

sudo pip install Mssql-scripter

Usage Guide

Help command:

mssql-scripter -h
usage: mssql-scripter [-h] [--connection-string  | -S ] [-d] [-U] [-P] [-f]
                  [--data-only | --schema-and-data]
                  [--script-create | --script-drop | --script-drop-create]
                  [--target-server-version {2005,2008,2008R2,2012,2014,2016,vNext,AzureDB,AzureDW}]
                  [--target-server-edition {Standard,PersonalExpress,Enterprise,Stretch}]
                  [--include-objects [[...]]] [--exclude-objects [[...]]]
                  [--ansi-padding] [--append] [--check-for-existence] [-r]
                  [--convert-uddts] [--include-dependencies] [--headers]
                  [--constraint-names] [--unsupported-statements]
                  [--object-schema] [--bindings] [--collation]
                  [--defaults] [--extended-properties] [--logins]
                  [--object-permissions] [--owner] [--use-database]
                  [--statistics] [--change-tracking] [--check-constraints]
                  [--data-compressions] [--foreign-keys]
                  [--full-text-indexes] [--indexes] [--primary-keys]
                  [--triggers] [--unique-keys] [--display-progress]
                  [--enable-toolsservice-logging] [--version]

Microsoft SQL Server Scripter Command Line Tool. Version 1.0.0a1

optional arguments:
  -h, --help            show this help message and exit
  --connection-string   Connection string of database to script. If connection
                        string and server are not supplied, defaults to value
                        in Environment Variable
                        MSSQL_SCRIPTER_CONNECTION_STRING.
  -S , --server         Server name.
  -d , --database       Database name.
  -U , --user           Login ID for server.
  -P , --password       Password.
  -f , --file           Output file name.
  --data-only           Generate scripts that contains data only.
  --schema-and-data     Generate scripts that contain schema and data.
  --script-create       Script object CREATE statements.
  --script-drop         Script object DROP statements
  --script-drop-create  Script object CREATE and DROP statements.
  --target-server-version {2005,2008,2008R2,2012,2014,2016,vNext,AzureDB,AzureDW}
                        Script only features compatible with the specified SQL
                        Version.
  --target-server-edition {Standard,PersonalExpress,Enterprise,Stretch}
                        Script only features compatible with the specified SQL
                        Server database edition.
  --include-objects [ [ ...]]
                        Database objects to include in script.
  --exclude-objects [ [ ...]]
                        Database objects to exclude from script.
  --ansi-padding        Generates ANSI Padding statements.
  --append              Append script to file.
  --check-for-existence
                        Check for database object existence.
  -r, --continue-on-error
                        Continue scripting on error.
  --convert-uddts       Convert user-defined data types to base types.
  --include-dependencies
                        Generate script for the dependent objects for each
                        object scripted.
  --headers             Include descriptive headers for each object scripted.
  --constraint-names    Include system constraint names to enforce declarative
                        referential integrity.
  --unsupported-statements
                        Include statements in the script that are not
                        supported on the target SQL Server Version.
  --object-schema       Prefix object names with the object schema.
  --bindings            Script options to set binding options.
  --collation           Script the objects that use collation.
  --defaults            Script the default values.
  --extended-properties
                        Script the extended properties for each object
                        scripted.
  --logins              Script all logins available on the server, passwords
                        will not be scripted.
  --object-permissions  Generate object-level permissions.
  --owner               Script owner for the objects.
  --use-database        Generate USE DATABASE statement.
  --statistics          Script all statistics.
  --change-tracking     Script the change tracking information.
  --check-constraints   Script the check constraints for each table or view
                        scripted.
  --data-compressions   Script the data compression information.
  --foreign-keys        Script the foreign keys for each table scripted.
  --full-text-indexes   Script the full-text indexes for each table or indexed
                        view scripted.
  --indexes             Script the indexes (XML and clustered) for each table
                        or indexed view scripted.
  --primary-keys        Script the primary keys for each table or view
                        scripted.
  --triggers            Script the triggers for each table or view scripted.
  --unique-keys         Script the unique keys for each table or view
                        scripted.
  --display-progress    Display scripting progress.
  --enable-toolsservice-logging
                        Enable verbose logging.
  --version             show program‘s version number and exit

Related examples:

• Dump Database Object Schema

# generate DDL scripts for all objects in the Adventureworks database and save the script to a file
mssql-scripter -S localhost -d AdventureWorks -U sa

# alternatively, specify the schema only flag to generate DDL scripts for all objects in the Adventureworks database and save the script to a file
mssql-scripter -S localhost -d AdventureWorks -U sa --schema-only

• Dump Database Object Data

# generate DDL scripts for all objects in the Adventureworks database and save the script to a file
mssql-scripter -S localhost -d AdventureWorks -U sa --data-only

• Dump the database object schema and data

# script the database schema and data to a file.
mssql-scripter -S localhost -d AdventureWorks -U sa --schema-and-data  > ./adventureworks.sql
 
# execute the generated above script with sqlcmd
sqlcmd -S mytestserver -U sa -i ./adventureworks.sql

• Include database Objects

# generate DDL scripts for objects that contain ‘Employee‘ in their name to stdout
mssql-scripter -S localhost -d AdventureWorks -U sa --include-objects Employee
# generate DDL scripts for the dbo schema and pipe the output to a file
mssql-scripter -S localhost -d AdventureWorks -U sa --include-objects dbo. > ./dboschema.sql

• Exclude database Objects

# generate DDL scripts for objects that do not contain ‘Sale‘ in their name to stdout
mssql-scripter -S localhost -d AdventureWorks -U sa --exclude-objects Sale

• Target Server version

# specify the version of SQL Server the script will be run against
mssql-scripter -S -U myUser -d AdventureWorks –target-server-version “SQL Azure DB” > myData.sql

• Target Server Edition

# specify the edition of SQL Server the script will be run against
mssql-scripter -S -U myUser -d devDB –target-server-edition “SQL Server Enterprise Edition” > myData.sql

• Pipe a generated script to SED

The following is the use of Linux and MacOS.

# specify the edition of SQL Server the script will be run against
mssql-scripter -S -U myUser -d devDB –target-server-edition “SQL Server Enterprise Edition” > myData.sql

• Script data to a file

# script all the data to a file.
mssql-scripter -S localhost -d AdventureWorks -U sa --data-only > ./adventureworks-data.sql

For more detailed usage guide or update please refer to: https://github.com/Microsoft/sql-xplat-cli/blob/dev/doc/usage_guide.md.

The following executes a command to see the effect of creating the SharePoint translation Service database's Create statement:

mssql-scripter --server 10.2.53.22\ZEUS --database 
‘TranslationService_cd4699102b0745ba81ca0cf72d9ffe6e‘ --user sa --password ‘1qaz2wsxE‘ --file 
E:\CreateTranslationServiceDatabase.sql
 

The file that executes the result can be downloaded here: Http://files.cnblogs.com/files/lavender000/CreateTranslationServiceDatabase.zip.

You can also set the connection string as an environment variable:

# set environment variable MSSQL_SCRIPTER_CONNECTION_STRING with a connection string.
export MSSQL_SCRIPTER_CONNECTION_STRING=‘Server=myserver;Database=mydb;User Id=myuser;Password=mypassword;‘
mssql-scripter 

# set environment variable MSSQL_SCRIPTER_PASSWORD so no password input is required.
export MSSQL_SCRIPTER_PASSWORD=‘ABC123‘
mssql-scripter -S localhost -d AdventureWorks -U sa

[Original articles, reproduced please indicate the source, for study purposes only, if there are errors please leave a message, thank you for your support]

[Original: Http://www.cnblogs.com/lavender000/p/6886560.html, from forever-smoked]

Mssql-scripter, a new SQL Server command-line tool that generates T-SQL scripts