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

这里向大家介绍一个新的生成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”选项：

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.us.debian.org/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 {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. Version1.0.0a1

optional arguments:-h, --help show this help message and exit--connection-string Connection stringof database to script. If connectionstringand server are not supplied, defaults to valueinEnvironment Variable

MSSQL_SCRIPTER_CONNECTION_STRING.-S , --server Server name.-d , --database Database name.-U , --user Login ID forserver.-P , --password Password.-f , --file Output filename.--data-only Generate scripts that contains data only.--schema-and-data Generate scripts that contain schema and data.--script-create Script objectCREATE statements.--script-drop Script objectDROP statements--script-drop-create Script objectCREATE 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 includeinscript.--exclude-objects [ [ ...]]

Database objects to exclude from script.--ansi-padding Generates ANSI Padding statements.--append Append script to file.--check-for-existence

Checkfor database objectexistence.-r, --continue-on-error

Continue scripting on error.--convert-uddts Convert user-defined data types to base types.--include-dependencies

Generate scriptfor the dependent objects foreachobjectscripted.--headers Include descriptive headers for each objectscripted.--constraint-names Include system constraint names to enforce declarative

referential integrity.--unsupported-statements

Include statementsinthe script that are not

supported on the target SQL Server Version.--object-schema Prefix object names with the objectschema.--bindings Script options to set binding options.--collation Script the objects that use collation.--defaults Script the default values.--extended-properties

Script the extended propertiesfor each objectscripted.--logins Script all logins available on the server, passwords

will not be scripted.--object-permissions Generate object-level permissions.--owner Script owner forthe objects.--use-database Generate USE DATABASE statement.--statistics Script all statistics.--change-tracking Script the change tracking information.--check-constraints Script the check constraints foreach table or view

scripted.--data-compressions Script the data compression information.--foreign-keys Script the foreign keys foreach table scripted.--full-text-indexes Script the full-text indexes foreach table or indexed

view scripted.--indexes Script the indexes (XML and clustered) foreach table

or indexed view scripted.--primary-keys Script the primary keys foreach table or view

scripted.--triggers Script the triggers foreach table or view scripted.--unique-keys Script the unique keys foreach 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 filemssql-scripter -S localhost -d AdventureWorks -U sa

# alternatively, specify the schema only flag to generate DDL scriptsfor all objects in the Adventureworks database and save the script to a filemssql-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 filemssql-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' intheir name to stdout

mssql-scripter -S localhost -d AdventureWorks -U sa --include-objects Employee

# generate DDL scriptsfor the dbo schema and pipe the output to a filemssql-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' intheir 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 inthe generated DDL script

#1) generate DDL scripts for all objects inthe Adventureworks database

#2) pipe generated script to sed and change all occurrences of SalesLT to SalesLT_test and save the script to a filemssql-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

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

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

执行结果的文件可以在这里下载。

完整PDF文档可以到Linux公社资源站下载：

------------------------------------------分割线------------------------------------------

具体下载目录在 /2017年资料/6月/16日/MSSQL-Scripter，一个新的生成T-SQL脚本的SQL Server命令行工具/

------------------------------------------分割线------------------------------------------

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

# 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