Rajendra Gupta
Get-DbaTable DBATools command

Script SQL Server objects using DBATools

June 20, 2019 by

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.

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. 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
  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

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.

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.

Sample table

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.

Get-DbaTable DBATools command

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

Get-DbaTable DBATools command

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

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.

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.

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.

Export-DbaScript DBATools command

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

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.

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 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.

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

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 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.

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.

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.

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.

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

Add constraints in object creation script

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

Add Non clustered indexes in object creation script

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

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

Add Foreign key in object creation script using 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.

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.

Add If Not Exists in object creation

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.

Specify a target version to generate a script

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.

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.

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

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
1,561 Views