Using Solutions in SSMS
16March 11, 2015 by Kenneth Fisher
There are a handful of scripts I use on a regular basis. Adam Mechanic’s (b/t) sp_WhoIsActive, My sp_DBPermissions and sp_SrvPermissions, my script for finding where backups are taken, Paul Brewer’s sp_RestoreGene to name a few. It gets tiresome at best to constantly be looking them up and copying the script over into a query window for me to use. In order to make my life easier I decided to start using a feature of SSMS (SQL Server Management Studio) that I’d heard of but never bothered to use before. SSMS has solutions just like Visual Studio. In this case I’m just using it a repository for frequently used scripts. Once I open my solution I have easy access to all of the scripts I most commonly use. And if I find a new one I need on a regular basis, say Kendra Little’s (b/t) sp_blitzindex, it’s easy enough to add.
To set up a solution in SSMS start by creating a new project.
I’m choosing to use the template for a “SQL Server Management Studio Project” for “SQL Server Scripts”. And I fill in the name and location of the new project. (If you have much experience with Visual Studio this is all going to look very familiar.)
I now have a blank solution. I add new queries to the Queries section, rename them (right click, select rename), add the script I want and save.
And now I have a list of commonly used scripts.
I should warn you that when you open SSMS and start working it creates a blank solution that get’s filled with any open query windows under Miscellaneous Files.
If you decide to open your Quick Scripts solution later in the day (let’s say you now have a dozen scripts open) then you need to be careful. The default option is to close your current solution, and that is going to close all of your currently open scripts. If you select the Add to Solution option then it will open the new solution along side your current Miscellaneous solution.
Now all you need to do to open up one of your scripts is double-click it in your solution. Handy right?
SQL Studies 
Excellent post. I use solutions all the time for my various scripts, broken down into sub projects. I find it very helpful.
Thank you for your work. Are you using 2012 version of ssms? In 2014 I am not seeing “Add to Solution” option. So my existing open queries are being closed every time I open frequently used script project. Any suggestion, am I missing something?
I don’t see the “Add to Solution” option unless I specifically open it under “File->Open->Project/Solution”. If I use “File->Recent Projects and Solutions” then I don’t get the option and it closes everything down. It’s annoying I agree. One thing you can do is modify your shortcut (or create one) that opens the project initially. That way it’s not an issue. Here is the link to the command line options for SSMS: https://msdn.microsoft.com/en-us/library/ms162825.aspx
[…] the shortcut to automatically open the project/solution that holds my scripts I collect a fair number of scripts and write even more. Using project(s) helps me keep them […]
Thanks for the post.
I have figured out you can add an existing .sql file to a project using “Add as Link” to prevent a copy being made into the project folder.
But can this be done somehow via a Drag-and-Drop from the Miscellaneuos Files folder?
Eg, If you were to drag-and-drop SQLQuery5.sql from Miscellaneuos Files in your example, it copies it into the project folder. Is there a way to preserve the original file location?
I tried Ctrl-Shift but that doesnt work.
Thanks
I honestly have no idea I’m afraid. If I run across anything I’ll let you know.
[…] Creating the solution […]
[…] I really should try using SSMS Solutions/Projects like Kenneth Fisher advocates, but I just haven’t gotten a chance yet. It would probably help with […]
[…] back I wrote about how to find where your backups are. And I’ve also written about I use a solution in SSMS to hold a bunch of my scripts. I was using this again today (I use it a lot!) and thought I would […]
For a general scripts that are used in different projects, Templates work much better. Solutions and Projects are more intended for isolated projects. Here’s a good link on Templates:
https://www.red-gate.com/simple-talk/blogs/i-thought-everyone-did-it-like-that-ssms-templates/
Snippets are also handy but I still like projects. For one thing I find it easier to copy them from place to place and organiz them. In the end though it’s all about what’s comfortable for you.
[…] I open SSMS. If you do not know how to create a solution with TSQL scripts and use it in SSMS, read this blog post by Kenneth Fisher (blog|twitter). I add my solution path and file name to the above runas instance […]
[…] Store your code in a solution. This one is somewhat personal. Not everyone wants to use a solution like I do. But make sure your commonly used code is saved and organized somewhere! […]
[…] he did mention talking about saving off scripts for reuse. But I’ve already talked about using solutions in SSMS to store my scripts. Heck, I even talked about creating a git repository for some of my […]
[…] use this all of the time. I have a solution in SSMS to store all of the queries I use on a regular basis. I open one of the queries and then pin it to the top to keep it sperate from the rest of my […]
[…] interview recently I was asked about which ones I use. All this together got me thinking. I keep a SSMS Solution with a bunch of scripts I’ve picked up over the years. I’ve become a bit of a magpie. I […]