Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

MSSQL-Scripter Tool and Examples to Generate Scripts for SQL Server Objects


By:   |   Updated: 2019-02-12   |   Comments   |   Related: More > Database Administration

Problem

Microsoft released a new multi-platform command tool MSSQL-Scripter, it is said to be the equivalent of the popular script generator wizard in SQL Server Management Studio as shown below.

Generate_Scripts Wizard in SSMS

This tool seems to provide a way for automating some common DBA tasks, how can we use it creatively to improve SQL Server DBA productivity?

Solution

Scripting out a SQL Server database object is a common task for DBAs and we need to script out an object for various reasons such as: a backup, review of objects or migration scripts.

Using the GUI wizard is not only time-consuming, but also error prone. With this command line tool, together with sqlcmd and some Windows command utility tools, we can come up with many decent solutions.

Installation of MSSQL-Scripter utility

There is a detailed installation guide here, but just two steps are really needed (using Windows installation as an example):

  1. Install the latest Python version (using 64bit installation)
  2. Once Python is installed, start a cmd.exe window and run the following (assuming you are connected to the internet).
pip install mssql-scripter			

Usage of MSSQL-Scripter utility

There is no detailed usage document for this utility, the best way to learn is through trial and error. Of course, the basic help information can be retrieved via running:

mssql-scripter -h			

And you will get the following:

Display the basic help info with -h parameter of mssql-scripter tool

We will use the Microsoft sample database WideWorldImporters, I have it installed on my local SQL Server instance, localhost\sql2016 or .\sql2016 for short.

Script out DML for a SQL Server table

We will first script out the table schema for Sales.CustomerCategories with this code:

mssql-scripter -S .\sql2016 -d WideWorldImporters --include-objects sales.CustomerCategories			

This will display the result on the console.

If we want to save the result to a file instead of the console, we just add parameter -f as shown below:

mssql-scripter -S .\sql2016 -d WideWorldImporters --include-objects sales.CustomerCategories -f c:\test\sales_CustCategory.sql			

We can open the generated SQL script with SSMS (or any text editor) for a quick check as shown below:

Display the generated script in SSMS

Script out Table Data in SQL Server

If we want to script out the data in the table, we can use parameter [--data-only] and this will generate a bunch of INSERT statements.

The following command will generate the insert scripts on the console:

mssql-scripter -S .\sql2016 -d WideWorldImporters --data-only --include-objects sales.CustomerCategories			

We will see this:

script data only

I find this is a very convenient when I need to move some configuration or reference tables from one server to another server.

The parameter names for this command utility are pretty clear, such as [--script-drop] means generating object drop statements, as shown below:

generate object dropping script

In the real world, this tool still has some limitations and we cannot get our needed result directly, so we may need to work creatively to get the work done. So here are a few cases.

Case 1: Script out SQL Server tables with wildcard characters for names

In [WildWorldImporters] database, we have the following tables whose names start with Customer:

use WideWorldImporters
select schema_name(schema_id) as [schema], name 
from sys.tables 
where name like 'Customer%'
order by name

We will get the following, 5 tables whose name starts with Customer:

Display db tables

Mssql-scripter does not support wildcard values for the parameters, i.e. I cannot do the following for [--include-objects] parameter:

mssql-scripter .\sql2016 -d WildWorldImporters --include-objects 'Sales.Customer%'			

To achieve the work, we need to take advantage of the Windows FOR command and use the sqlcmd utility.

We will use sqlcmd to find all tables were name like 'Customer%' and get the result into the mssql-scripter [--include-objects] parameter.

Here is the one-line script (we assume all the scripts will be written to one file called customer_tbl.sql):

for /F "usebackq skip=2" %t in (`sqlcmd -S .\sql2016 -d WideWorldImporters -Q "set nocount on; select schema_name(schema_id)+'.'+name from sys.tables where name like 'customer%' and schema_id=schema_id('sales')"`) do mssql-scripter -S .\sql2016 -d WideWorldImporters --include-objects %t >> c:\test\customer_tbl.sql

If I run the command, I will see the result like this:

customer categories

I can see I am scripting out 5 tables whose names start with 'Customer' and generating a file named c:\test\customer_tbl.sql.

A few key things:

  • The "usebackq" option means that we will use back quote ` to indicate the content is a command line to be executed, in this case it is the sqlcmd command.
  • The "skip=2" option means we will skip the first two records returned from the command result.
    • We know when we run any query like "select * from MyTable" with sqlcmd, the first two records will be the column names (first record) followed by a dash line (second record), like the following (I highlighted the first two records).
display the column names and additional dot line

Case 2: Script out a subset of records from one SQL Server table

Sometimes you do not need all the records from a table, for example, you may only need records for a specific day or for a specific user. So, to generate a script for these records, we cannot use mssql-scripter utility directly, but we can make this into three tasks.

  1. Use select .. into to dump the data to a temporary table in TempDB
  2. Use mssql-scripter utility to generate the script from the table in TempDB
  3. Use sqlcmd utility to drop the temporary table created from step 1 in Tempdb

So, let's say we want to generate a script for the first 5 records from the Sales.CustomerCategory table, we can use the following command lines.

sqlcmd -S .\sql2016 -d WideWorldImporters -Q "select top 5 * into tempdb.dbo.CustomerCategories from sales.CustomerCategories"
mssql-scripter -S .\sql2016 -d tempdb --include-objects dbo.CustomerCategories --data-only -f c:\test\CustCat_top5.sql --exclude-use-database
sqlcmd -S .\sql2016 -d TempDB -Q "drop table dbo.CustomerCategories"
			
demo of case 2

Of course, in the first command I use tempdb.dbo.CustomerCategories, if we want the table schema to be [Sales] instead of [dbo], we need to create the schema in tempdb first if it does not exist.

Opening the file c:\test\CustCat_top5.sql, I will get the following code:

INSERT [dbo].[CustomerCategories] ([CustomerCategoryID], [CustomerCategoryName], [LastEditedBy], [ValidFrom], [ValidTo]) VALUES (1, N'Agent', 1, CAST(N'2013-01-01T00:00:00.0000000' AS DateTime2), CAST(N'9999-12-31T23:59:59.9999999' AS DateTime2))
INSERT [dbo].[CustomerCategories] ([CustomerCategoryID], [CustomerCategoryName], [LastEditedBy], [ValidFrom], [ValidTo]) VALUES (2, N'Wholesaler', 1, CAST(N'2013-01-01T00:00:00.0000000' AS DateTime2), CAST(N'9999-12-31T23:59:59.9999999' AS DateTime2))
INSERT [dbo].[CustomerCategories] ([CustomerCategoryID], [CustomerCategoryName], [LastEditedBy], [ValidFrom], [ValidTo]) VALUES (3, N'Novelty Shop', 1, CAST(N'2013-01-01T00:00:00.0000000' AS DateTime2), CAST(N'9999-12-31T23:59:59.9999999' AS DateTime2))
INSERT [dbo].[CustomerCategories] ([CustomerCategoryID], [CustomerCategoryName], [LastEditedBy], [ValidFrom], [ValidTo]) VALUES (4, N'Supermarket', 1, CAST(N'2013-01-01T00:00:00.0000000' AS DateTime2), CAST(N'9999-12-31T23:59:59.9999999' AS DateTime2))
INSERT [dbo].[CustomerCategories] ([CustomerCategoryID], [CustomerCategoryName], [LastEditedBy], [ValidFrom], [ValidTo]) VALUES (5, N'Computer Store', 1, CAST(N'2013-01-01T00:00:00.0000000' AS DateTime2), CAST(N'9999-12-31T23:59:59.9999999' AS DateTime2))
			

Case 3: Script out SQL Server indexes on a table

The mssql-scripter utility currently cannot script out indexes or table triggers directly, indexes/triggers are scripted out together with the host tables. But I often need to script out indexes for various reasons, and I usually rely on PowerShell and SMO to do the work. But actually, with the mssql-scripter utility and with some command line utilities, we can do the same thing. This is very handy for people who do not want to bother to learn SMO.

The basic algorithm is as follows:

  1. Generate a script for table creation including indexes creation
  2. Generate a script for table creation without any index creation
  3. Count the lines of the script in step 2, say the line count is [X]
  4. Remove top [X] lines from script in step 1, what remains will be the index creation script.

We will rely on Windows commands to do the task.

Step 1: generate the table creation script (indexes included), c:\test\customer.sql.

mssql-scripter -S .\sql2016 -d WideWorldImporters --include-objects sales.customers --exclude-check-constraints --exclude-foreign-keys --exclude-extended-properties --exclude-defaults -f c:\test\customer.sql
			

Step 2: generate the table creation script (just the table itself without index creation).

mssql-scripter -S .\sql2016 -d WideWorldImporters --include-objects sales.customers --exclude-check-constraints --exclude-foreign-keys --exclude-extended-properties --exclude-defaults --exclude-indexes -f c:\test\customer_tbl.sql
			

Step 3: count the lines of the script in c:\test\customer_tbl.sql

for /f "usebackq" %x in (`find /v /c "" ^<c:\test\customer_tbl.sql`) do @echo %x && set LOC=%x			

This command displays the lines of code via @echo %x and then sets the environment variable LOC to be %x. Here is the result:

demo of case 3

In this command, we use the Windows command FIND to count the number of lines.

If I open the c:\test\custmer_tbl.sql in SSMS, I can see the total lines is indeed 54 as shown below.

customer

Step 4: remove the top 54 lines from c:\test\customer.sql.

more +%LOC% < c:\test\customer.sql > c:\test\cust_idx.sql			

Here %LOC% has the value of 54 as [LOC] variable is defined in step 3.

If we open c:\test\customer.sql in SSMS, we can find that starting from line 55, we indeed have the index creation script as shown below:

statistics

After step 4 is done, if we open the newly created c:\test\cust_idx.sql in SSMS, we can see the following:

demo the final result

Note, [Sales.Customer] table actually has two more indexes, one is a clustered index and the other a unique index, which are not generated by the mssql-scripter utility.  The reason is these two indexes are actually driven or generated automatically due to the table definition, i.e. two constraints, one is the primary key constraint (resulting in the clustered index) and the other is a unique constraint (resulting in a unique index). This can be seen in the table definition:

CREATE TABLE [Sales].[Customers](
   [CustomerID] [int] NOT NULL,
   [CustomerName] [nvarchar](100) NOT NULL,
   [BillToCustomerID] [int] NOT NULL,
   [CustomerCategoryID] [int] NOT NULL,
   [BuyingGroupID] [int] NULL,
   [PrimaryContactPersonID] [int] NOT NULL,
   [AlternateContactPersonID] [int] NULL,
   [DeliveryMethodID] [int] NOT NULL,
   [DeliveryCityID] [int] NOT NULL,
   [PostalCityID] [int] NOT NULL,
   [CreditLimit] [decimal](18, 2) NULL,
   [AccountOpenedDate] [date] NOT NULL,
   [StandardDiscountPercentage] [decimal](18, 3) NOT NULL,
   [IsStatementSent] [bit] NOT NULL,
   [IsOnCreditHold] [bit] NOT NULL,
   [PaymentDays] [int] NOT NULL,
   [PhoneNumber] [nvarchar](20) NOT NULL,
   [FaxNumber] [nvarchar](20) NOT NULL,
   [DeliveryRun] [nvarchar](5) NULL,
   [RunPosition] [nvarchar](5) NULL,
   [WebsiteURL] [nvarchar](256) NOT NULL,
   [DeliveryAddressLine1] [nvarchar](60) NOT NULL,
   [DeliveryAddressLine2] [nvarchar](60) NULL,
   [DeliveryPostalCode] [nvarchar](10) NOT NULL,
   [DeliveryLocation] [geography] NULL,
   [PostalAddressLine1] [nvarchar](60) NOT NULL,
   [PostalAddressLine2] [nvarchar](60) NULL,
   [PostalPostalCode] [nvarchar](10) NOT NULL,
   [LastEditedBy] [int] NOT NULL,
   [ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
   [ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_Sales_Customers] PRIMARY KEY CLUSTERED 
(
   [CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USERDATA],
 CONSTRAINT [UQ_Sales_Customers_CustomerName] UNIQUE NONCLUSTERED 
(
   [CustomerName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USERDATA],
   PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
) ON [USERDATA] TEXTIMAGE_ON [USERDATA]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [Sales].[Customers_Archive] )
)
GO
			

As you can see, you cannot create these two indexes scripts via the create index statement if the table is already defined this way.

Summary

In this tip, we have introduced the mssql-scripter utility and explored ways to use this tool creatively to compensate for its limitations.

This utility is most useful if we want to generate a script for migrating a database object, both schema and data.

Currently the mssql-scripter utility is still in its early stages, meaning there are lots of deficiencies and it is still not widely used, but I believe if it is used more frequently by more DBAs, Microsoft will invest more on this multi-platform utility. For example, it can script out logins, but without hashed passwords, which makes the login script feature almost useless.

Next Steps

With SQL Server available on multiple platforms, we DBAs need to get familiar with these new cross-platform utilities, so we can create administration scripts that can be used across platforms as it is quite possible, we may have SQL Server on different platforms.

The following links provide some relevant details for this tip:



Last Updated: 2019-02-12


get scripts

next tip button



About the author
MSSQLTips author Jeffrey Yao Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools