阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

230次阅读
没有评论

共计 7963 个字符,预计需要花费 20 分钟才能阅读完成。

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

相关例子:

  • 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
# 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 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 DatabaseCREATE语句:

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

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

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

—————————————— 分割线 ——————————————

免费下载地址在 http://linux.linuxidc.com/

用户名与密码都是www.linuxidc.com

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

下载方法见 http://www.linuxidc.com/Linux/2013-07/87684.htm

—————————————— 分割线 ——————————————

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

# 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.linuxidc.com/Linux/2017-06/144840.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计7963字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中