Since SSMS is built upon Visual Studio, many of the features available to Visual Studio are also available to SSMS. The first one that I want to talk about is Solutions.
Are you the type of person that has all of your custom queries in one folder, and finding the particular one that you are looking for can sometimes be a pain? Well, solutions can certainly help you. A solution consists of one or more projects, which then contain files. In SSMS, the project can contain Connections, Queries and Miscellaneous files. These various projects can be used to group your queries so that they are easily accessible.
Personally, I maintain two different solutions. One is for all the presentations that I do, each in a separate project within that solution. The other is all of my day-to-day scripts.
The first step in using a solution is to open up the Solution Explorer window. This is available from the View Menu | Solution Explorer, or by pressing the keyboard shortcut Ctrl+Alt+L.
Assuming that you don’t have any query windows open yet, the Solutions Explorer window will be empty:
If you open a query window, then the default solution “Solution1” will show up in the Solution:
At this point, you can right-click the solution to add a project to the solution. You can either create a new project or add an existing one:
In this example, I’ll create a new one. You will get a dialog asking where to store this project and what to name it. I like to name the project for the purpose of it, and I like to put them on my OneDrive so that they are always available to sync to other computers. You can also select the type of scripts that this project will contain.
You now have a solution with one project consisting of three empty folders.
At this point, you can right-click the Queries folder to create a new query:
Right clicking the Connections folder will allow you to create a connection to a server. Right-clicking the project will allow you to add new or existing items, connections or queries.
Pro Tip: Clear out the connections on a regular basis to help with the opening speed of the queries. It’s also important to do this before sharing the solution with anyone.
The Queries folder will hold all files with a “.sql” extension. The Miscellaneous folder will hold all other files. In my projects, I may have text, PowerShell or DOS command files; this folder is where they will reside.
When you close the solution, or close SSMS, you will be prompted to save the solution. You can also manually save the solution from menu options in the file menu:
Since I have mentioned my solutions a couple of times, here is what one of my solutions looks like:
Here you can see how I have them broken down into various groupings for the scripts.
When you are just opening up SSMS, you might need to open up your solution. Just navigate over to the File menu, and go down to Recent Projects and Solutions. There you can select from your recently opened solutions and projects:
In conclusion, Solutions in SSMS allow you to organize your script collection for faster access. Furthermore, if you use one for presentations, it prevents you from having to jump back and forth between a Windows Explorer window where you have your scripts and SSMS.
This post is for day one of my month-long blog series “A Month of SSMS Tips”. I have a landing page for the series at bit.ly/MonthOfSSMS. Please visit this page for an easy place to quickly view all of the other tips in this series.