使用DBATools编写SQL Server对象脚本

This article gives an overview to generate scripts for SQL Server objects with Windows PowerShell tool DBATools.

本文概述了如何使用Windows PowerShell工具DBATools为SQL Server对象生成脚本。

Database administrators or developers require generating scripts for SQL Server objects. We might need scripts to store a copy of object script before object change, create specific objects into other database environments such as development, UAT or non-prod environment. It is an excellent practice to keep a copy of the object before making a change to it. We can easily refer to the old script and roll back if required. Usually, we use SSMS Generate Scripts wizard to get these scripts.

数据库管理员或开发人员需要为SQL Server对象生成脚本。 在对象更改之前,我们可能需要脚本来存储对象脚本的副本,在其他数据库环境(例如开发,UAT或非生产环境)中创建特定的对象。 在更改对象之前保留对象的副本是一种极好的实践。 我们可以轻松地参考旧脚本并在需要时回滚。 通常,我们使用SSMS生成脚本向导来获取这些脚本。

在SSMS中生成脚本向导 (Generate Script Wizard in SSMS)

Let’s have a quick review of Generate Scripts Wizard in SSMS.

让我们快速回顾一下SSMS中的“生成脚本向导”。

Right click on a database and go to Tasks and Generate Scripts.

右键单击数据库,然后转到“任务和生成脚本”。

Generate Script Wizard in SSMS

It gives the option to script the entire database or specific database object.

它提供了对整个数据库或特定数据库对象编写脚本的选项。

Choose Objects

Select specific database objects to script out and click on Next. We might select multiple objects as well to script out together.

选择要编写脚本的特定数据库对象,然后单击“下一步”。 我们也可以选择多个对象来一起编写脚本。

Select the database object to script

In the next page, it gives us the scripting options. We get the following options.

在下一页中,它提供了脚本选项。 我们有以下选择。

Set Scripting options
  1. Single file per object to generate all scripts in different files 每个对象一个文件”来生成不同文件中的所有脚本
  2. Save to clipboard: we can save the generated script to the clipboard using this option

    保存到剪贴板:我们可以使用此选项将生成的脚本保存到剪贴板
  3. Save to New query window: It generates the script and opens it in a new query window of SSMS

    保存到新查询窗口:它将生成脚本并在SSMS的新查询窗口中将其打开

Click on Advanced to set advanced scripting options. On this page, you can set various options to generate scripts. A few relevant options are as follows.

单击高级以设置高级脚本选项。 在此页面上,您可以设置各种选项来生成脚本。 一些相关的选项如下。

  • Script for the server version

    服务器版本的脚本
  • Script primary and foreign keys

    脚本主键和外键
  • Script change tracking

    脚本更改跟踪
  • Script primary, unique keys

    脚本主唯一键
  • Types of data to script – Schema only, Data Only and Schema with Data

    要编写脚本的数据类型–仅架构,仅数据和具有数据的架构

Advanced scripting options

On the next page, we can review the configuration and finish to generate object scripts.

在下一页上,我们可以查看配置并完成生成对象脚本。

Review your selections to generate script

We need to repeat the same process depending upon the requirements of objects script. We might need to set options different for few objects. We need to follow this wizard for a specific object in this case. It might be a time-consuming process to do it.

我们需要根据对象脚本的要求重复相同的过程。 我们可能需要为几个对象设置不同的选项。 在这种情况下,我们需要针对特定​​对象遵循此向导。 这样做可能会很耗时。

This approach also works on instance level only. We need to do this task only for each SQL Server instance. We cannot use this task with multiple instances altogether.

此方法也仅适用于实例级别。 我们只需要对每个SQL Server实例执行此任务。 我们不能将这个任务与多个实例一起使用。

In this article, I will use the AdventureWorks2017 database and HumanResources.Employee table. We can see that this table contains Primary, foreign keys, clustered, non-clustered index and triggers.

在本文中,我将使用AdventureWorks2017数据库和HumanResources.Employee表。 我们可以看到该表包含主键,外键,聚集的,非聚集的索引和触发器。

Sample table

In this case, we can use PowerShell open-source module DBATools to do this task for us.

在这种情况下,我们可以使用PowerShell开源模块DBATools为我们完成此任务。

DBATools生成对象脚本 (DBATools to generate object scripts)

In my previous articles on DBAtools, we explored a few essential commands to do tasks in SQL Server. We need to use a combination of commands to generate object scripts using DBATools.

在我先前关于DBAtools的文章中,我们探讨了一些在SQL Server中执行任务的基本命令。 我们需要使用命令的组合来使用DBATools生成对象脚本。

  • Get-DbaTable

    获取数据库表

We use Get-Help command in DBATools to get search commands containing the keyword.

我们在DBATools中使用Get-Help命令来获取包含关键字的搜索命令。

> Get-Help *table*

Get-DbaTable DBATools command

You can find the synopsis and syntax of the Get-DbaTable command in DBATools.

您可以在DBATools中找到Get-DbaTable命令的摘要和语法。

Get-DbaTable DBATools command

Let’s run this command to get information about the HumanResources.Employee table.

让我们运行此命令以获取有关HumanResources.Employee表的信息。

In the following query, we use the following parameters.

在以下查询中,我们使用以下参数。

  • SqlInstance: We specify the SQL instance using this parameter

    SqlInstance:我们使用此参数指定SQL实例
  • Database: We can specify the database name in this parameter

    数据库:我们可以在此参数中指定数据库名称
  • Table: Specify table for which we want to generate a script

    表格:指定要为其生成脚本的表格
> Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee

In the output, we can see that we get a piece of information about the index and data space, row count along with the table properties information such as FILETABLE, memory optimized, partition table, change tracking.

在输出中,我们可以看到关于索引和数据空间,行数以及表属性信息(例如FILETABLE,内存优化,分区表,更改跟踪)的信息。

table information using the Get-DbaTable

We require generating scripts for the object. We need to use Get-DbaDbTable with the Export-DbaScript command to generate a script for the object.

我们需要为对象生成脚本。 我们需要将Get-DbaDbTable与Export-DbaScript命令一起使用 为对象生成脚本。

DBATools command Export-DbaScript allows exports of SQL Server objects from SQL Management Objects (SMO).

DBATools命令Export-DbaScript允许从SQL管理对象(SMO) 导出 SQL Server对象。

Let’s check the synopsis and syntax of Export-DbaScript with the following command.

让我们使用以下命令检查Export-DbaScript的简介和语法。

> Get-help Export-DbaScript

Export-DbaScript DBATools command

Let’s execute DBATools commands Get-DbaDbTable and Export-DbaScript to generate a script for the object.

让我们执行DBATools命令Get-DbaDbTableExport-DbaScript来为对象生成脚本。

> Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee | Export-DbaScript -Passthru 

In this script, we use -Passthru parameter to display script in the window itself.

在此脚本中,我们使用-Passthru参数在窗口本身中显示脚本。

It generates the object script; however, we did not get scripts for keys, constraints, indexes. This script might not be useful for us because it does not replicate the source objects and gives only basis object creation script.

它生成对象脚本; 但是,我们没有获得有关键,约束,索引的脚本。 该脚本可能对我们没有用,因为它不会复制源对象,并且仅提供基础对象创建脚本。

Generate script using DBATools

If we execute the above command without -Passthru parameter, it saves the script in the current user context. You can go to the directory and open the script in SSMS to go through it.

如果我们在不带-Passthru参数的情况下执行上述命令,则会将脚本保存在当前用户上下文中。 您可以转到目录并在SSMS中打开脚本以进行浏览。

Passthru parameter to display script

In the SSMS Generate Script Wizard, we set the scripting options under the Advanced section. In the DBATools also, we can set the scripting options using the new command New-DbaScriptingOption.

在SSMS生成脚本向导中,我们在“ 高级”部分下设置脚本选项。 同样在DBATools中,我们可以使用新命令New-DbaScriptingOption设置脚本选项

Let’s explore this command to set scripting options and generate the desired script.

让我们探索该命令来设置脚本选项并生成所需的脚本。

New-DbaScriptingOption命令DBATools (New-DbaScriptingOption command DBATools)

In the following screenshot, we can check the details about the New-DbaScriptingOption with the following the command

在以下屏幕截图中,我们可以使用以下命令检查有关New-DbaScriptingOption的详细信息

>Get-help New-DbaScriptingOption -examples 

New-DbaScriptingOption command DBATools

We can check the available scripting options using the Get-Member command. Execute the following command to get a list of available properties along with their definitions.

我们可以使用Get-Member命令检查可用的脚本选项。 执行以下命令以获取可用属性及其定义的列表。

> $options = New-DbaScriptingOption
>$options | Get-Member

Get members details of all available properties

Get members details of all available properties

We can check the value of individual property as well. For example, let’s check the value of the property DriClustered.

我们也可以检查单个财产的价值。 例如,让我们检查属性DriClustered的值。

> $options = New-DbaScriptingOption
> $options.DriClustered

We get the return value False for the DriClustered parameter. It is the reason that the generated script using DBATools does not contain the clustered index information.

我们获得DriClustered参数的返回值False 。 这是使用DBATools生成的脚本不包含聚集索引信息的原因。

set $options.DriClustered to true using DBATools

Similarly, we can check value for other properties.

同样,我们可以检查其他属性的值。

set $options.DriClustered to true using DBATools

We can change the value of required properties to TRUE and use $options object along with the parameter –ScriptingOptionsObject to generate the script with these objects.

我们可以将必需属性的值更改为TRUE,并使用$ options对象以及参数ScriptingOptionsObject来生成包含这些对象的脚本。

使用DBATools在对象创建脚本中添加约束 (Add constraints in object creation script using DBATools)

Suppose we want to add all constraints in the object script. Execute the following script to change the value to TRUE for DriAllConstraints and generate the script.

假设我们要在对象脚本中添加所有约束。 执行以下脚本,将DriAllConstraints的值更改为TRUE并生成脚本。

> $options = New-DbaScriptingOption
> $options.DriAllConstraints =$true
> Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee | Export-DbaScript -Passthru -ScriptingOptionsObject $options

We can see constraints as well in the script for the specified object.

我们还可以在脚本中看到指定对象的约束。

Add constraints in object creation script

使用DBATools在对象创建脚本中添加非聚集索引 (Add Non clustered indexes in object creation script using DBATools)

Let’s do the following things for this example.

让我们为这个示例做以下事情。

  • DriAllConstraints property to false DriAllConstraints属性更改为false
  • NonClusteredIndexes property to true NonClusteredIndexes属性更改为true
> $options = New-DbaScriptingOptionPS 
> $options.DriAllConstraints =$false
> $options.NonClusteredIndexes  =$true
>  Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee | Export-DbaScript -Passthru -ScriptingOptionsObject $options

Add Non clustered indexes in object creation script

使用DBATools在对象创建脚本中添加外键 (Add Foreign key in object creation script using DBATools)

Let’s try with a few other interesting options. Suppose we want foreign keys in the object scripts. We need to enable DriForeignKeys parameter and execute the script as follows

让我们尝试其他一些有趣的选项。 假设我们要在对象脚本中使用外键。 我们需要启用DriForeignKeys参数并执行脚本,如下所示

> $options.DriForeignKeys = $true
> Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee | Export-DbaScript -Passthru -ScriptingOptionsObject $options

In the output, we can see foreign key constraints along with the object creating script.

在输出中,我们可以看到外键约束以及对象创建脚本。

Add Foreign key in object creation script using DBATools

使用DBATools在对象创建脚本中添加如果不存在 (Add If Not Exists in object creation script using DBATools)

It is a good practice to check whether the object exists or not before we create an object. We might have another object with a similar name. We use SQL Exists operator to test the existence of an object in the SQL Server database.

在创建对象之前,先检查对象是否存在是一个好习惯。 我们可能还有另一个名称相似的对象。 我们使用SQL Exists运算符来测试SQL Server数据库中对象的存在。

Our script should include Not Exists operator, and we should create an object if it does not exists. We need to enable parameter IncludeIfNotExists to the true and generated script will contain the IF EXISTS clause.

我们的脚本应包括Not Exists运算符,并且如果不存在则应创建一个对象。 我们需要启用参数IncludeIfNotExists为true,并且生成的脚本将包含IF EXISTS子句。

>$options.IncludeIfNotExists = $true
> Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee | Export-DbaScript -Passthru -ScriptingOptionsObject $options

Add If Not Exists in object creation

指定目标版本以使用DBATools生成脚本 (Specify a target version to generate a script using DBATools)

We might have a different version of the destination SQL Server for which we want to generate a script. For example, I want to generate a script for SQL Server Compatibility level 140, whereas the source compatibility level is 150.

我们可能要为其生成脚本的目标SQL Server具有不同版本。 例如,我想为SQL Server兼容性级别140生成脚本,而源兼容性级别为150。

We want to set the TargetServerVersion parameter for the SQL Server compatibility level we want to generate the script.

我们想要为要生成脚本SQL Server兼容性级别设置TargetServerVersion参数。

>$options.TargetServerVersion = "Version140"
>Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee | Export-DbaScript -Passthru -ScriptingOptionsObject $options

Specify a target version to generate a script

使用DBATools一起为多个对象生成脚本 (Generate scripts for multiple objects together using DBATools)

In previous examples, we generated a script for an object using DBATools. We might want to generate scripts for multiple objects. We can select multiple objects in the Generate Scripts wizard of SSMS. In the DBATools also we can do it using variables.

在前面的示例中,我们使用DBATools为对象生成了脚本。 我们可能想为多个对象生成脚本。 我们可以在SSMS的“生成脚本”向导中选择多个对象。 在DBATools中,我们也可以使用变量来实现。

In the following query, we defined a $Tablename variable, and it includes two table names. We use Foreach-object loop to go through each table and generate the required script. We can consider Foreach-object loop similar to a loop in SQL Server.

在以下查询中,我们定义了$ Tablename变量,它包含两个表名。 我们使用Foreach-object循环遍历每个表并生成所需的脚本。 我们可以考虑将Foreach对象循环类似于SQL Server中的循环。

> $TableName = "HumanResources.Employee", 'Person.Person';
> $options = New-DbaScriptingOption
> $options.DriForeignKeys = $true
> $TableName | Foreach-Object
>> Get-DbaDbTable -ServerInstance Kashish\SQL2019CTP -Database AdventureWorks2017 $SourceDB -Table $_ | Export-DbaScript -ScriptingOptionsObject $options -Passthru;
>>}

In the following screenshot, we can see it generated scripts for both the objects.

在下面的屏幕截图中,我们可以看到它为两个对象生成了脚本。

Generate scripts for multiple objects together using DBATools

结论 (Conclusion)

In this article, we explored to generate a script using DBATools Windows PowerShell commands. We can use DBATools to automate these scripts and run as per our requirements. I would suggest reviewing them as per your environment. If you have any comments or questions, feel free to leave them in the comments below.

在本文中,我们探索了使用DBATools Windows PowerShell命令生成脚本的方法。 我们可以使用DBATools来自动化这些脚本并按照我们的要求运行。 我建议根据您的环境对其进行审查。 如果您有任何意见或疑问,请随时将其留在下面的评论中。

目录 (Table of contents)

DBATools PowerShell Module for SQL Server
PowerShell SQL Server Validation Utility – DBAChecks
SQL Database Backups using PowerShell Module – DBATools
IDENTITY columns threshold using PowerShell SQL Server DBATools
DBATools PowerShell SQL Server Database Backups commands
SQL Restore Database using DBATools
Validate backups with SQL restore database operations using DBATools
Fix Orphan users in SQL Server using DBATools PowerShell
Creating a SQL Server Database using DBATools
Get SQL Database details using DBATools
Get-DbaHelpIndex command in DBATools
Script SQL Server objects using DBATools
适用于SQL Server的DBATools PowerShell模块
PowerShell SQL Server验证实用程序– DBAChecks
使用PowerShell模块SQL数据库备份– DBATools
使用PowerShell SQL Server DBATools的IDENTITY列阈值
DBATools PowerShell SQL Server数据库备份命令
使用DBAToolsSQL Restore Database
使用DBATools通过SQL恢复数据库操作验证备份
使用DBATools PowerShell修复SQL Server中的孤立用户
使用DBATools创建SQL Server数据库
使用DBATools获取SQL数据库详细信息
DBATools中的Get-DbaHelpIndex命令
使用DBATools编写SQL Server对象脚本

翻译自: https://www.sqlshack.com/script-sql-server-objects-using-dbatools/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
软件名称:DBATools For PL/SQL Developer 发布版本:1.1.0 Beta 1 发布日期:2010-01-28 软件简介: PL/SQL Developer是Oracle数据库当前最流行的开发工具之一,它在ORACLE数据库开发设计方面功能强大,使用方便,但是数据库管理方面一直比较欠缺。 DBATools For PL/SQL Developer 是一款PL/SQL Developer的辅助插件,主要功能是提供许多DBA数据库管理的功能,使ORACLE工程师不仅可以使用PL/SQL Developer进行开发设计,同时也可以进行数据库管理及监控,大大提高工作效率。 软件安装说明: 本插件为绿色软件,将DBATools.dll和DBAToolsConfig.xml文件复制到PL/SQL Developer安装目录的PlugIns子目录即可。例如:PL/SQL Developer安装在C:\Program Files\PLSQL Developer目录,那将DBATools.dll和DBAToolsConfig.xml文件复制到C:\Program Files\PLSQL Developer\PlugIns目录下,然后重启PL/SQL Developer就可以使用。 注: 1.本软件只支持PL/SQL Developer7.0及以上的版本。 --------------------------------------------------------------- [2010-01-28]发布 DBATools For PL/SQL Developer 1.1.0 Beta 1版本更新说明 1.+ 对像快捷菜单增加了Oracle加密对像解密功能[仅支持Oracle10g] 2.# 优化了部份数据库监视SQL 3.* 修正了V1.0.0正式版中列表中数字列按字符排序的BUG --------------------------------------------------------------- [2009-01-22]发布 DBATools For PL/SQL Developer 1.0.0 正式版本更新说明 --------------------------------------------------------------- 功能列表: 1.表空间管理 2.初始化参数管理 3.重做日志管理 4.数据库监视 4.1.查看SGA统计信息 4.2.查看排序情况 4.3.查看日志切换情况 4.4.查看锁资源 4.11.查看库缓存命中率 4.6.查看数据缓存命中率 4.7.查看WorkArea情况 4.8.查看当前会话等待事件 4.9.查看数据库大小 4.10.查看等待事件统计信息 5.清空缓冲区 6.导出数据库表结构文档 7.快速打开TNSNAME.ORA文件 8.快速打开listener.ORA文件 9.快速打开Oracle Net Manager 10.自定义配置 10.1.语言配置,支持中英两种语言 10.2.菜单自定义配置 10.3.数据库监视SQL自定义配置 11.快捷菜单 11.1.表 11.1.1.分析表 11.1.2.取记录数 11.1.3.创建物化视图日志 11.1.4.设置并行度 11.2.视图 11.2.1.取记录数 11.3.物化视图 11.3.1.分析 11.3.2.取记录数 11.3.3.刷新 11.3.4.设置并行度 11.4.数据库链接 11.4.1.测试 11.11.列 11.11.1.分析 11.6.索引 11.6.1.分析 11.6.2.重建 11.7.表空间 11.7.1.管理 11.7.2.新增 11.7.3.编辑

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值