Do you ever find yourself working on different scripts at once? Maybe you are working on something that tests certain functionality in SQL Server, you have some presentation scripts you are working on, and possibly three or four scripts related to a project at work. I find myself in a situation like this from time to time – multiple scripts open checking this or that.
How do you organize your scripts? Do you leave them all open in SSMS? Do you create a folder structure on your disk somewhere and set yourself a reminder task? I have left them all open in SSMS on occasion because I wanted to come back to them and work on them and it can help remind me where I am in the thought process and what needs to be done next.
Did you know that there is a feature in SSMS to help you with organizing yourself? It’s not there just to help you get organized – but it can also help with efficiency and recoverability of files. I’ll explain the recoverability a bit later.
SSMS has a feature called “Solution Explorer.” Much like Visual Studio or BIDs (which is Visual Studio), you have the ability to create a solution file to manage your projects. This solution can then be stored on disk and updated as you progress through the solution or projects.
If you want to display this feature, click the View Menu from the toolbar and then select “Solution Explorer.” This will open a new pane in your management studio from which you can add new or existing projects as well as rename the solution. To the right is an example of one of my solutions. In this image, you can see that I have two projects displayed associated with this particular solution. In these projects, I have loaded numerous script files for each of the projects as I work on them. Some of these scripts are testing the results of stored procs, while others may be stored procs, and yet others may be tables. What the script performs immaterial to Solution Explorer.
Should I decide that I need multiple files open from the project, I can highlight them all and open them all at once. Nothing earth shattering, but now I have an easy way to see the files associated with each task or project as I work on the task/project.
The project files are stored in XML format and are saved off to disk in a directory of your liking. An unfortunate problem with this kind of storage is the sorting of your scripts in the project. Despite this problem there is a way to fix it. I found the fix for that at this blog. It is nice to have scripts sorted properly to your liking. I recommend giving that blog a read and I would recommend the use of this feature in SSMS – if you aren’t doing something already (for instance in Visual Studio, SVN or something like that).
I like having the ability to see all of the scripts quickly that I may need on the same screen I am working in. I can bounce quickly between the scripts as needed and I don’t necessarily have to keep them open indefinitely while working out the task at hand.
I mentioned earlier that it also helps with the recoverability of files. Let me explain a bit. Have you ever been in the middle of working on a script and then the dreaded forced shutdown occurs due to updates? Just in case you did not get your files saved, SSMS has an autorecover feature as well as an autosave feature. Files that are Autorecover and Autosave (recovered) are saved commonly in Solution1. They are stored in your user profile directory and can be brought back so you can continue working on them.
Should you need to find these files, here is a common path you can use to retrieve them.
%userprofile%DocumentsSQL Server Management StudioBackup FilesSolution1
Do you want to learn more about this feature? Check out the Microsoft documentation on the topic. You can view that here.
Something that I really like about this feature is the ability to create a script either directly in the project, add an existing script, or take a script that you started working on outside of the project and move it into the project from within SSMS. It took me a bit of looking to find how to add a script from SSMS directly to the project (when not initiated from within the project). It is really easy to do once you find it. Here is how to do it.
With the focus in SSMS active on the script you wish to move, click on the File menu. From the File menu you will have an option to Move the script that you are currently working on in SSMS. From there Select the appropriate project and it is that simple.
*Note: In the example of the image being displayed, you can see that the query was SQLQuery1.sql that I wanted to move.
