This article gives an overview to generate scripts for SQL Server objects with Windows PowerShell tool DBATools.
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.
Generate Script Wizard in SSMS
Let’s have a quick review of Generate Scripts Wizard in SSMS.
Right click on a database and go to Tasks and Generate Scripts.
It gives the option to script the entire database or specific database object.
Select specific database objects to script out and click on Next. We might select multiple objects as well to script out together.
In the next page, it gives us the scripting options. We get the following options.
- Save to file: Select this option to save the script as a file. If we want to generate scripts for multiple objects together, it generates all scripts in a single SQL file. We can use option Single file per object to generate all scripts in different files
- Save to clipboard: we can save the generated script to the clipboard using this option
- Save to New query window: It generates the script and opens it in a new query window of 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
On the next page, we can review the configuration and finish to generate object scripts.
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.
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.
In this case, we can use PowerShell open-source module DBATools to do this task for us.
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.
- Get-DbaTable
We use Get-Help command in DBATools to get search commands containing the keyword.
|
1 |
> Get-Help *table* |
You can find the synopsis and syntax of the Get-DbaTable command in DBATools.
Let’s run this command to get information about the HumanResources.Employee table.
In the following query, we use the following parameters.
- SqlInstance: We specify the SQL instance using this parameter
- Database: We can specify the database name in this parameter
- Table: Specify table for which we want to generate a script
|
1 |
> 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.
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.
DBATools command Export-DbaScript allows exports of SQL Server objects from SQL Management Objects (SMO).
Let’s check the synopsis and syntax of Export-DbaScript with the following command.
|
1 |
> Get-help Export-DbaScript |
Let’s execute DBATools commands Get-DbaDbTable and Export-DbaScript to generate a script for the object.
|
1 |
> 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.
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.
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.
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.
Let’s explore this command to set scripting options and generate the desired script.
New-DbaScriptingOption command DBATools
In the following screenshot, we can check the details about the New-DbaScriptingOption with the following the command
|
1 |
>Get-help New-DbaScriptingOption -examples |
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.
|
1 2 |
> $options = New-DbaScriptingOption >$options | Get-Member |
We can check the value of individual property as well. For example, let’s check the value of the property DriClustered.
|
1 2 |
> $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.
Similarly, we can check value for other properties.
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.
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.
|
1 2 3 |
> $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 Non clustered indexes in object creation script using DBATools
Let’s do the following things for this example.
- We do not want to add constraints in the script, therefore, change constraint DriAllConstraints property to false
- We want to add all non-clustered indexes in the script, therefore change constraint NonClusteredIndexes property to true
|
1 2 3 4 |
> $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 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
|
1 2 |
> $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 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.
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.
|
1 2 |
>$options.IncludeIfNotExists = $true > Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee | Export-DbaScript -Passthru -ScriptingOptionsObject $options |
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.
We want to set the TargetServerVersion parameter for the SQL Server compatibility level we want to generate the script.
|
1 2 |
>$options.TargetServerVersion = "Version140" >Get-DbaDbTable -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Table HumanResources.Employee | Export-DbaScript -Passthru -ScriptingOptionsObject $options |
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.
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.
|
1 2 3 4 5 6 |
> $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.
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.
Table of contents
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023