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.
SQL Server Management Studio Projects
We can organize T-SQL codes using SSMS projects. Go to New-> Project.
It opens a new project configuration window. Here, we do the following configurations.
- Choose either SQL Server Scripts or Analysis Services Script. Let’s select SQL Server Scripts for this article
- Specify a project name
- Specify a directory to save this project
Click OK, and it opens the Solution Explorer to manage the project and its resources.
If this solution explorer is not visible, go to View-> Solution Explorer. You can also use keyboard shortcut Ctrl+Alt+L.
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.
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.
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.
It opens the SSMS SQL Server connection window. Specify SQL Instance, Authentication (Windows or SQL ), and connect to the instance.
It shows the instance in the connection window.
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.
Let’s specify a system stored procedure SP_HELPDB in the new query window. Click on Execute or press F5 to execute the query.
Right-click on the query title and save it.
Once you save the query, it shows a green color in front of the query text.
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.
Here, we name the file as sp_helpdb.sql
Similarly, create another SQL file and save it, as shown below. You can store many files in the solution.
Let’s close the SSMS and open it again. To open existing projects, click on File – Open -> Project/Solution.
It takes you the default directory for the projects.
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.
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.
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.
Let’s click on an HTML file, and you can see it under the Miscellaneous files as shown below.
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.
It opens the solution folder, and you see both projects created in the solution.
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.
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.
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.
Let’s add sp_who2.sql file using option Add as Link.
Now, open the sp_who2.sql file for the Demo project and change it as follows.
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.
Change the default view of Projects and Solutions in SSMS
By default, it shows the solution and project in visual studio style.
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.
You can click on a solution file to view the XML content of the 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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023