MSSQL-Scripter，一个新的生成T-SQL脚本的SQL Server命令行工具

时间：2022-04-15 04:58:01

这里向大家介绍一个新的生成T-SQL脚本的SQL Server命令行工具：mssql-scripter。它支持在SQL Server、Azure SQL DB以及Azure SQL DW中为数据库生成CREATE和INSERT T-SQL脚本。

Mssql-scripter是一个跨平台的命令行工具，功能等同于SQL Server Management Studio中的Generate and Publish Scripts Wizard。

咱们能够在Linux、macOS和Windows上使用它生成数据定义语言（DDL-Data Definition Language）和数据操纵语言（DML – Data Manipulation Language），并且生成的T-SQL脚本可以运行在所有平台的SQL Server、Azure SQL Database、以及Azure SQL Data Warehouse中。

Installation

1.   Windows

a)       安装Python，最新安装包下载地址：https://www.python.org/downloads/，注意安装的时候要选择”Add Python to PATH”选项：

MSSQL-Scripter，一个新的生成T-SQL脚本的SQL Server命令行工具

b)      安装mssql-scripter，命令行里执行下面命令：

pip install mssql-scripter

2.   Linux

a)      检查pip版本，是否是9.0及其以上：

pip –version

b)      如果pip未安装或者版本低于9.0，使用如下命令安装以及升级版本：

sudo apt-get install python-pip

sudo pip install --upgrade pip

c)      安装mssql-scripter：

sudo pip install mssql-scripter

如果系统是Ubuntu或者Debian，需要安装libunwind8软件包：

Ubuntu 14 & 17

执行如下命令：

sudo apt-get update

sudo apt-get install libunwind8

Debian 8（暂时没有环境，未测试）

文件‘/etc/apt/sources.list’需要更新：

deb http://ftp.us.debian.org/debian/ jessie main

执行如下命令：

sudo apt-get update

sudo apt-get install libunwind8

3.   macOS（暂时没有环境，未测试）

a)       检查pip版本，是否是9.0及其以上：

pip –version

b)      如果pip未安装或者版本低于9.0，使用如下命令安装以及升级版本：

sudo apt-get install python-pip

sudo pip install --upgrade pip

c)       安装mssql-scripter：

sudo pip install mssql-scripter

Usage Guide

帮助命令：

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 {,,2008R2,,,,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 {,,2008R2,,,,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

相关例子：

  • 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

下面这个是Linux和macOS的用法。

# change a schema name in the generated DDL script

# ) generate DDL scripts for all objects in the Adventureworks database

# ) pipe generated script to sed and change all occurrences of SalesLT to SalesLT_test and save the script to a file

mssql-scripter scripter -S localhost -d Adventureworks -U sa | sed -e "s/SalesLT./SalesLT_test./g" > adventureworks_SalesLT_test.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

更详细的Usage Guide或更新请参考：https://github.com/Microsoft/sql-xplat-cli/blob/dev/doc/usage_guide.md。

下面执行一个命令看看效果，生成SharePoint Translation Service Database的CREATE语句：

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

执行结果的文件可以在这里下载：http://files.cnblogs.com/files/lavender000/CreateTranslationServiceDatabase.zip。

另外还可以把连接字符串设置成环境变量：

# 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

[原创文章，转载请注明出处，仅供学习研究之用，如有错误请留言，谢谢支持]

[原文：http://www.cnblogs.com/lavender000/p/6886560.html，来自永远薰薰]