MSSQL-Scripter，一個新的產生T-SQL指令碼的SQL Server命令列工具

標籤：pes   tar   pip   evel   options   測試   xtend   blank   研究   

這裡向大家介紹一個新的產生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上使用它產生資料定義語言 (Data Definition Language)（DDL-Data Definition Language）和資料操縱語言（DML – Data Manipulation Language），並且產生的T-SQL指令碼可以運行在所有平台的SQL Server、Azure SQL Database、以及Azure SQL Data Warehouse中。

 

Installation1.   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 -husage: 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.0a1optional 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

相關例子：

• 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 scripts for 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 sqlcmdsqlcmd -S mytestserver -U sa -i ./adventureworks.sql

• Include database objects

# generate DDL scripts for objects that contain ‘Employee‘ in their name to stdoutmssql-scripter -S localhost -d AdventureWorks -U sa --include-objects Employee# generate DDL scripts for 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‘ in their name to stdoutmssql-scripter -S localhost -d AdventureWorks -U sa --exclude-objects Sale

• Target server version

# specify the version of SQL Server the script will be run againstmssql-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 againstmssql-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# 1) generate DDL scripts for all objects in the 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

更詳細的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，來自永遠薰薰]

MSSQL-Scripter，一個新的產生T-SQL指令碼的SQL Server命令列工具