SQL Management Studio has gives users the ability to script out entire database structures via a UI. Right click a database, select Tasks and then Generate Scripts.
We can also do this via the command line using a Python based tool called mssql-scripter. If Python isn’t already installed you can use this link – the following option screen comes up, in this example we’ll use the install 3.7.4 for Windows.
Once installed we then need to use the Python package installer to download and install mssql-scripter, in a command prompt window enter the following command: pip install mssql-scripter
Done, and as we’re keeping things nice and up to date we’ll use the following command to update pip as well:
python -m pip install –upgrade pip
And that’s all there is to it, we can now issue a command via the command line to script out entire databases. On my machine I’ll use the following command to script out the AdevtureWorks2016 database on my default instance to a SQL script file (adventureworks2016.sql):
mssql-scripter -S localhost -d AdventureWorks2016 >./adventureworks2016.sql –display-progress
Here’s a sample snippet of the exported file open in SSMS:
In all the mssql-scripter completed in under 30 seconds which is pretty impressive. It’s very flexible too, mssql-scripter -h will return the options for the command including being able to script schema and data as well excluding many different object types.
Mssql-scripter is a great way to use fast database object scripting functionality and we’ve used it for migration and release processes.