这里向大家介绍一个新的生成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，最新安装包下载地址：/downloads/，注意安装的时候要选择”Add Python to PATH”选项：

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

pip install mssql-scripter

2. Linux

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

pip –version

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

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

c) 安装mssql-scripter：

sudo pip install mssql-scripter

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

Ubuntu 14 & 17

执行如下命令：

sudo apt-get updatesudo apt-get install libunwind8

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

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

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

执行如下命令：

sudo apt-get updatesudo apt-get install libunwind8

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

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

pip –version

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

sudo apt-get install python-pipsudo 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 {,,R2,,,,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, --helpshow 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 , --serverServer name.

-d , --database Database name.

-U , --userLogin ID for server.

-P , --password Password.

-f , --fileOutput file name.

--data-onlyGenerate scripts that contains data only.

--schema-and-data Generate scripts that contain schema and data.

--script-create Script object CREATE statements.

--script-dropScript object DROP statements

--script-drop-create Script object CREATE and DROP statements.

--target-server-version {,,R2,,,,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.

--bindingsScript options to set binding options.

--collationScript the objects that use collation.

--defaultsScript 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.

--statisticsScript 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.

--triggersScript the triggers for each table or view scripted.

--unique-keysScript 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

相关例子：

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

Script data to a file

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

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

执行结果的文件可以在这里下载：/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

原文地址：/lavender000/p/6886560.html

.NET社区新闻，深度好文，微信中搜索dotNET跨平台或扫描二维码关注