Using Solutions in SSMS

6

March 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.

Solution1

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.)

Solution2

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.

Solution3

And now I have a list of commonly used scripts.

Solution4

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.

Solution5

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.

Solution6

Solution7

Now all you need to do to open up one of your scripts is double-click it in your solution. Handy right?

6 thoughts on “Using Solutions in SSMS

  1. brimhj says:

    Excellent post. I use solutions all the time for my various scripts, broken down into sub projects. I find it very helpful.

  2. Taiob says:

    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

  3. […] 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 […]

  4. machineabs says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,664 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: