Rajendra Gupta
open existing file

Overview of Solutions and Projects in SSMS

March 30, 2020 by

DBAs and developers manage a broad set of queries, codes, applications. It is a good practice to organize code or t-SQL scripts so that we can easily access them without wasting time searching for them. You might use Visual Studio, GitHub source control for it. In this article, we look at how Solutions and Projects help to organize T-SQL scripts.

I am using the latest general availability release SSMS 18.4. You can download it from Microsoft docs.

SSMS general availability release

SQL Server Management Studio Projects

We can organize T-SQL codes using SSMS projects. Go to New-> Project.

Create a new project

It opens a new project configuration window. Here, we do the following configurations.

  1. Choose either SQL Server Scripts or Analysis Services Script. Let’s select SQL Server Scripts for this article
  2. Specify a project name
  3. Specify a directory to save this project

Choose SQL Server Scripts

Click OK, and it opens the Solution Explorer to manage the project and its resources.

Solution Explorer

If this solution explorer is not visible, go to View-> Solution Explorer. You can also use keyboard shortcut Ctrl+Alt+L.

Go to View-> Solution Explorer

The solution is a logical grouping of project-related resources. By default, SQL Server creates a solution for every project with a similar name. We can create multiple projects in a solution. Let’s create a new project in the same solution.

Go to File -> New -> Project. It opens the same new project window. Here, Select Add to Solution to add the project into exiting solution.

Select Add to Solution

Click OK, and it adds a new project into the existing project, as shown below. Here, we see each project contains different objects: Connections, Queries, and Miscellaneous.

Adds a new project

Connections

In the connection tab, we can create multiple SQL Server connections for different environments, servers. Suppose you have a Dev, Test, and Production environment, and we can run queries on any environment based on our requirement.

Right-click on Connections and New Connection.

Click on a new Connections

It opens the SSMS SQL Server connection window. Specify SQL Instance, Authentication (Windows or SQL ), and connect to the instance.

SQL Server connection properties

It shows the instance in the connection window.

View instance in solution

Queries

In the query tab, we can create multiple SQL query files. You can either click on a connection to open a new query window.

Queries folder

Let’s specify a system stored procedure SP_HELPDB in the new query window. Click on Execute or press F5 to execute the query.

Create a new query file

Right-click on the query title and save it.

Save the query

Once you save the query, it shows a green color in front of the query text.

Green mark

Right-click on the query file and rename it. You should give it a familiar name so that you can easily identify queries from the file name. It is advantageous if you have a large number of query files.

Rename the file

Here, we name the file as sp_helpdb.sql

File rename example

Similarly, create another SQL file and save it, as shown below. You can store many files in the solution.

Create another SQL

Let’s close the SSMS and open it again. To open existing projects, click on File – Open -> Project/Solution.

Relaunch SSMS

It takes you the default directory for the projects.

Project directory

Select the specific project (in this case – SQLShack) and Open it. Here, it shows a separate folder for each project in SSMS, as shown in the following image. It also has a solution file (extension .ssmsln). Select this solution file and open it.

Select the specific project

It opens the solution explorer with existing connections, SQL files, and miscellaneous files. You can double-click on existing files and start working on these.

open existing file

Miscellaneous Files

If we have any external files for the project, it appears in the Miscellaneous files section. You might not be aware that, using SSMS, we can create, modify a lot of files other than SQL query files. Click on File-> New -> File and it gives a list of files such as Text, HTML page, JavaScript, XML. Icon, Cursor, VbClass files.

Miscellaneous Files

Let’s click on an HTML file, and you can see it under the Miscellaneous files as shown below.

View Miscellaneous Files

Copy files from a project to another in a solution

As I already stated, we can create multiple projects in a single project. We created two projects [Demo Project] and [SQLShack] in the current solution. Suppose we created these projects for the test and production environment.

Initially, we added the SQL file and miscellaneous files in the test project (Demo Project). Once we completed development work for specific files, we want to move them into the production project (SQLShack). We can easily copy the files between the projects in a solution.

Right-click on the destination project and go to Existing item.

Copy files from a project to another in a solution

It opens the solution folder, and you see both projects created in the solution.

opens the solution folder

Open the source project (in this case, Demo Project) and select the SQL file you wish to copy in the SQLShack project. Here, we wish to copy the sp_helpdb.sql file in the SQLShack project.

Open the source project

Click on Add, and you can see the file in the SQLShack project as well. In this case, sp_helpdb.sql file in both projects is independent of each other. If you modify a file in one project, it does not change the second project file.

Click on Add

Add a link option to reference files from other projects

Sometimes, we want to reference another project file. If someone does any changes in the existing file, changes should be visible in another project as well. For this, we can click on Add as Link, as shown below.

Add as Link

Let’s add sp_who2.sql file using option Add as Link.

click on Add as Link

Now, open the sp_who2.sql file for the Demo project and change it as follows.

Change the file content

Save the changes and close the .sql file. Now, open the sp_who2.sql file from the SQLShack project. We added this file as a link from the [Demo Project]. As shown in the following screenshot, changes are visible in this project as well.

Verify content

Change the default view of Projects and Solutions in SSMS

By default, it shows the solution and project in visual studio style.

Change the default view of Project

Click on the highlighted folder icon, and it changes the look, as shown below. In this view, we can see the solution directory, solution file, and project files. It does not group files in a different folder in a project.

New view

You can click on a solution file to view the XML content of the file.

XML Content of solution file

Conclusion

In this article, we explored Projects and Solutions in SSMS to organize different projects, files, and connections. We can also manage miscellaneous files such as text, HTML, XML using this feature.

Rajendra Gupta
SQL Server Management Studio (SSMS)

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

5,247 Views