By: Jeffrey Yao | Updated: 2019-02-12 | Comments (1) | 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.
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):
- Install the latest Python version (using 64bit installation)
- 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:
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:
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:
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:
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:
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:
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).
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.
- Use select .. into to dump the data to a temporary table in TempDB
- Use mssql-scripter utility to generate the script from the table in TempDB
- 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"
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:
- Generate a script for table creation including indexes creation
- Generate a script for table creation without any index creation
- Count the lines of the script in step 2, say the line count is [X]
- 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:
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.
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:
After step 4 is done, if we open the newly created c:\test\cust_idx.sql in SSMS, we can see the following:
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:
- For Loop Command
- Copy a SQL Server database with just the objects and no data
- Transfer SQL Server database schema objects and data with SMO
- Generate T-SQL Scripts for all SQL Server Databases and all Objects using PowerShell
- As homework, see if you can script out indexes or triggers from multiple tables.
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2019-02-12
