I’m puzzled that so few people use script projects and solutions when working with SQL Server Management Studio (SSMS).
They are easy to use. Let’s see an example:
Instead of just starting to create scripts, from the File menu, click New, then Project. You are greeted with the new Project dialog which also allows you to create a solution.
I’ve selected SQL Server Scripts as the project template. Note there is also one for Analysis Services scripts. I’ve named the project, picked a location, and chosen to create a new solution. I might choose to create a solution with a different name if it will contain multiple projects. In this case, I’m not doing that.
To get to the point faster, here’s one that I created earlier:
There are three sections of interest:
Connections – keeps details of all connections used by the project. I typically only have one for any project but I can imagine scenarios where I would have more. A big advantage of this is that if I need to change the connection, I do it once, and all scripts here will use it when I open them.
Queries – as the name says. A nice addition in recent versions is that it automagically keeps them shown in alphabetical order. It didn’t used to do that so we had to remove them all and put them back in.
Miscellaneous – you’ll find if you right-click this that there’s nothing of interest. So how does something get there I hear you ask? Well it’s when you right-click the project and ask to add an existing item.
If you pick any file apart from those with a .sql extension (such as a text file), they’ll be placed into the Miscellaneous folder.
These projects are a great free built-in way to manage your scripts.