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?

Filed under: Documentation, Microsoft SQL Server, SQLServerPedia Syndication, SSMS Tagged: microsoft sql server, SSMS