Database code is often buried inside of our systems and rarely viewed. Many people just depend on the code being inside a database and accessing it for modifications by clicking an object in Management Studio (SSMS). This has worked fine, but also resulted in various problems during development. Previous versions aren't tracked or code is littered with commented out lines, or one developer may overwrite another's changes without being aware of this happening.
A version control system (VCS) is the most common tool that many developers use to track their code changes. Extracting code from a SQL Server database, however, can be cumbersome. There are third party tools, such as SQL Source Control, that can make this easy, but this article will examine how we can quickly extract code from a database using the native SSMS capabilities.
Note: This article uses SSMS 17.6, but previous versions use a similar process.
Extracting All Objects
In this section we will extract all objects from a database and store them in separate files. We will work with the WideWorldImporters sample database.
To get started, let's first right click our database in the Object Explorer. When we do this, we see a number of items in the context menu. We want to select the Tasks option, which will open a submenu. From here, click the Generate Scripts item.
Once you select this item, a new dialog wizard will open. There is a standard welcome screen, shown here, which you can mark as not to be shown again. I recommend you check that box.
The next screen is where you choose whether to script the entire database or specific objects. In this case, we are getting all objects, so we will leave this alone and script the entire database.
From here, we move on to the options for scripting. This dialog allows us to choose where to store our code. We have four choices here. We can save code:
- in text files
- publish to a web service
- copy the code to the clipboard
- copy the code into a new query window
The choices are laid out strangely, as you can see below.
I have never tried to publish to a web service, and I don't think the Hosting Toolkit is still being maintained. I'm not sure of the purpose of this option, but if you would like use this, I've be interested to know why and how well it works.
The clipboard and new query window options are useful if you need to manipulate the code and execute it on another database right away. They will put all the code in the destination as a single set of objects, with batches delimited by GO.
The file option is what we will choose here. As you can see above, I've checked the "save scripts to a specific location" option. I've also selected the radio button to separate each object into its own file. This makes viewing code easier than having all objects in one file.
My location is a folder that I can see from SSMS. In this case, this is a git repository that is empty. Here is my folder view before scripting the code.
The last option is to choose to store this as Unicode or ANSI text. I rarely deal with Unicode, but I haven't had issues storing each object in this format. If I ever needed to work with Unicode, I'd like the files to be compatible, so I'd leave this alone.
There is an advanced button as well. If you click this, then you will see a number of options for scripting, including the SQL Server version. I tend to leave these alone, as these settings are useful, and more importantly, people often forget to change them on different SSMS instances. As a result, if you export code from another version of SSMS, or another developer does, you might end up with code conflicts. However, if you want to change anything, you an do so by clicking the item and changing from True to False, or selecting another item.
The exceptions that you might consider changing are at the bottom of the list. There are Table/View options, some of which are not set by default. For example, triggers aren't scripted, but should be. You may with to enablie this, as well as Full-Text indexes, compression options, and more.
Once your options are set, you can close this dialog and return to the Script Options tab. Click next to move to a summary of our choices. As you can see below, I've expanded the Table/View options.Many people blow through this screen, but if you are doing this for the first time, make sure these are the settings that are appropriate for your environment.
Once you click Next here, the scripting process will start. Progress is written to the window as each object is scripted. When complete, the Finish button will be active and will close the window when clicked.
If I now to back to my git repo, I'll see this view.
Note that each object is listed in its own file with the [schema].[object].[type] naming standard. Unfortunately, I can't alter this standard and need to live with it. I also cannot script the different object types into separate folders.
If I open one of these in SSMS, say the Application.Cities.Table.sql file, I see this:
There are indexes and other related code below this.
Commiting Code
Each type of VCS has it's own method of committing versions of files. For git, I need to stage the files (with git add) and the commit them. I'll do this from the CLI. If you use a different VCS, there will be a CLI or client to help with this.
All the files are listed below this in the window, but there are too many to show.
There aren't any changes in the folder at this point, but if I interrogate the git repo, all the files are committed. At this point, I can open one of them, change something and then go through the process of saving the changes. The details of that process are for another article.
Conclusion
This is a short tutorial on getting code from your database into a git VCS repository using SSMS. This is likely a one time task for you, as once the objects are scripted here, you may not want to script them all again. You could, and unchanged objects shouldn't be picked up as changes.
In a future article, we'll look at scripting specific objects and objects types in SSMS. We will also delve into using automation techniques to perform this scripting without requiring SSMS or human interaction.
