Installing Python and using the new command line T-SQL script generator

Earlier today two new command line tools were announced for SQL Server, one an experimental Linux tools DBFS which enables access to live DMVs without using a UI like SSMS and secondly a tool that enables script generation of objects within SQL rather like the Generate SQL Scripts option in SSMS.

In this post I'm going to run through the installation of the script generator tool and provide a very quick demo. The reason I'm going through this is because in order to install the tool we need to use something called PIP. PIP is a package management system that enables us install and use packages written in Python. Yeah, Python again! 

So if you haven't already installed Python, because it isn't available by default in Windows you'll need to get it from here:

https://www.python.org/downloads/

This will take you to the download screen and here you get two version options; 2.7.13 or 3.6.1, I went for the latter and there is a link that explains the differences between version 2 and version 3 of Python: https://wiki.python.org/moin/Python2orPython3

And this is the installation screen, before hitting the install option make sure the Add Python 3.6 to PATH is selected otherwise things won't be working as expected after install.

Nothing untoward in the actual installation and we get a successful message in a couple of minutes.

So now that Python is installed we need to install the mssql-scripter package and that is very straightforward and rather familiar to those using PowerShell I guess. We use the command: pip install mssql-scripter from a Windows command prompt ran as administrator.

The installation package goes and does its thing!

Again, after a couple of minutes we get a couple of done messages and returned to the command prompt, installation all good.

Once complete we can pass in a -h parameter to the mssql-scripter command and see a wide array of options that are available to us but in this demo I'll keep it simple and you can refer to the original link to see the different ways of using the tools.

To use the command I've typed out the following which will create a script (adventureworks.sql) in my working directory containing the DDL and DML commands for the objects in the AdventureWorks2014 database:

mssql-scripter -S localhost -d AdventureWorks2014  > ./adventureworks2014.sql

Worth noting that although mssql-scripter is brand new I am using this on a SQL Server 2014 instance. 

The script ran in just a few seconds which is very impressive and it produced the file as expected and whilst I'll not put the entire script content in this post here is a screenshot of the script opened inside SSMS.

Now I did work through some of the examples on the release article however I did run into unrecognized argument errors when using -schema-and-data which is shame as I did want to test that functionality. I'll await a comment back on that one and then perhaps run through some more scenarios of using the command.

Edit: I did parse the created script: 

Command(s) completed successfully.