I have found several times creating the same set of scripts for the same thing, trying to locate where I saved it, when did I last run it. I end up spending ages looking for some code I ran previously and then end up writing it again when I can’t find it anyway.
Until I started using Solutions in Management Studio. Again lots of screen shots and explanations.
Setting up solutions Click on New Project
Enter the name of the Project and a file location. I called them “My SQL scripts” in the examples shown
If you have a solution open it will ask you to save, even if you didn’t realise it, SQL has a default solution. Click no ( if you haven’t anything you want to save)
A new solution will be created. You might need to go to the menu and select View Solution explorer if its not already open
We can now get clever with this and add new projects under this. You can now add multiple solutions to the project (so like folders to keep different script in)
I gave my first one a name of daily scripts
Give it a name , do this for all the sub category’s of scripts you use.
I have mocked up below a
Daily scripts
Weekly scripts
Monthly Reports
Scripts I run against particular database
As shown below I can keep scripts in this structure, the clever thing when you connect to a particular server, it remembers the connections, so the script actually opens up when double clicking on that server you last ran the script against!
The DBA team go one further and we share a combined version in source control , and we can share common scripts between us all.
Save the project, by File Save MY SQL Scripts ( or what ever you called it)
Each one of the folders is a separate projects, you just need to ensure when you open the project you choose the main project.
You can also browse ( or create a short cut to the location of the MY SQL Scripts.ssmssln file ( Make sure it’s the correct one) as there are two. If you double click it opens management studio with all the projects
These can be fiddly initially setup at times ( When i first did one I deleted and started again) , but once setup and working they make life easier to keep all those scripts you use regularly !
