Lately I have to use SQL Server for a Microservices based system that I am currently building. This is the first time in my 15 years of software development life I had to use SQL Server. I was looking for a tool that can help me generate database schema from the already created database schema. I wanted to do store schema in my version control system. The usual tool to do this kind of work is SQL Server Management Studio but it works only on Windows operating system. Since I use Mac I was looking for a tool that is cross platform. My search led me to discover mssql-scripter, a multi-platform command line experience for scripting SQL Server databases.
In this post I will give you a quick introduction to mssql-scripter that you can then use in your development workflow.
Let’s first start by running SQL Server in a docker container.
$ docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=cE4LQ50e0s' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest
You don’t have to type
$.$signifies command-prompt.
The above will start the SQL Server in a docker container and forward port 1433 on the local machine.
mssql-scripter is a Python CLI tool so you need to have Python 3 installed on your machine. I don’t pollute global Python installation on my machine so I will create a virtual env.
$ virtualenv -p python3 venv
$ source venv/bin/activate
(venv) $
Now, we can install mssql-scriper using pip
(venv) $ pip install mssql-scripter
You can look at the help command to know more about the mssql-scripter utility.
(venv) $ mssql-scripter -h
usage: mssql-scripter [-h] [--connection-string | -S ] [-d] [-U] [-P] [-f]
[--file-per-object] [--data-only | --schema-and-data]
[--script-create | --script-drop | --script-drop-create]
[--target-server-version {2005,2008,2008R2,2012,2014,2016,vNext,AzureDB,AzureDW}]
[--target-server-edition {Standard,Personal,Express,Enterprise,Stretch}]
[--include-objects [[...]]] [--exclude-objects [[...]]]
[--include-schemas [[...]]] [--exclude-schemas [[...]]]
[--include-types [[...]]] [--exclude-types [[...]]]
[--ansi-padding] [--append] [--check-for-existence] [-r]
[--convert-uddts] [--include-dependencies]
[--exclude-headers] [--constraint-names]
[--unsupported-statements]
[--disable-schema-qualification] [--bindings]
[--collation] [--exclude-defaults]
[--exclude-extended-properties] [--logins]
[--object-permissions] [--owner]
[--exclude-use-database] [--statistics]
[--change-tracking] [--exclude-check-constraints]
[--data-compressions] [--exclude-foreign-keys]
[--exclude-full-text-indexes] [--exclude-indexes]
[--exclude-primary-keys] [--exclude-triggers]
[--exclude-unique-keys] [--display-progress]
[--enable-toolsservice-logging] [--version]
Let’s first create BikeStores database schema mentioned in the tutorial at sqlservertutorial.net.
I used DBeaver GUI tool to create database schema and load data into it.
Now, let’s use mssql-scripter to generate database schema for the BikeStores database.
(venv) $ mssql-scripter -S localhost --user sa --password cE4LQ50e0s --database BikeStores -f ./schema.sql
The above will create a file schema.sql in the current directory. If you don’t specify -f option then the output will be rendered on the terminal.
The sample output of the schema.sql is shown below.
USE [BikeStores]
GO
/****** Object: Schema [production] Script Date: 6/9/20 12:08:05 AM ******/
CREATE SCHEMA [production]
GO
/****** Object: Schema [sales] Script Date: 6/9/20 12:08:05 AM ******/
CREATE SCHEMA [sales]
GO
/****** Object: Table [production].[brands] Script Date: 6/9/20 12:08:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [production].[brands](
[brand_id] [int] IDENTITY(1,1) NOT NULL,
[brand_name] [varchar](255) NOT NULL,
PRIMARY KEY CLUSTERED
(
[brand_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [production].[categories] Script Date: 6/9/20 12:08:05 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [production].[categories](
[category_id] [int] IDENTITY(1,1) NOT NULL,
[category_name] [varchar](255) NOT NULL,
PRIMARY KEY CLUSTERED
(
[category_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
If you want data along with the schema then you can run the above command with --schema-and-data option as shown below.
(venv) $ mssql-scripter -S localhost --user sa --password cE4LQ50e0s --database BikeStores --schema-and-data -f ./schema-and-data.sql
The sample output of the above command is shown below.
CREATE TABLE [sales].[stores](
[store_id] [int] IDENTITY(1,1) NOT NULL,
[store_name] [varchar](255) NOT NULL,
[phone] [varchar](25) NULL,
[email] [varchar](255) NULL,
[street] [varchar](255) NULL,
[city] [varchar](255) NULL,
[state] [varchar](10) NULL,
[zip_code] [varchar](5) NULL,
PRIMARY KEY CLUSTERED
(
[store_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [production].[brands] ON
INSERT [production].[brands] ([brand_id], [brand_name]) VALUES (1, N'Electra')
INSERT [production].[brands] ([brand_id], [brand_name]) VALUES (2, N'Haro')
INSERT [production].[brands] ([brand_id], [brand_name]) VALUES (3, N'Heller')
INSERT [production].[brands] ([brand_id], [brand_name]) VALUES (4, N'Pure Cycles')
INSERT [production].[brands] ([brand_id], [brand_name]) VALUES (5, N'Ritchey')
INSERT [production].[brands] ([brand_id], [brand_name]) VALUES (6, N'Strider')
INSERT [production].[brands] ([brand_id], [brand_name]) VALUES (7, N'Sun Bicycles')
INSERT [production].[brands] ([brand_id], [brand_name]) VALUES (8, N'Surly')
INSERT [production].[brands] ([brand_id], [brand_name]) VALUES (9, N'Trek')
SET IDENTITY_INSERT [production].[brands] OFF
SET IDENTITY_INSERT [production].[categories] ON
If we only want to generate schema and data for sales schema then we can use --include-schemas option as shown below.
(venv) $ mssql-scripter -S localhost --user sa --password cE4LQ50e0s --database BikeStores --include-schemas sales --schema-and-data -f ./schema-and-data-sales.sql
The above will not generate DDL and DML scripts for production schema.
Conclusion
In this post I showed you how you can use mssql-scripter to generate DDL and DML statements.
