Using SQL Server Management Studio Projects

One of the most underutilized assets in SQL Server Management Studio is the SSMS project. Within Management Studio, one can create code projects that make organizing SQL scripts much simpler, and more easily integrate with source control systems. In this brief post, I’ll show the essentials of using SQL Server Management Studio projects for T-SQL code management.

T-SQL Code Management

Too often, T-SQL code management is neglected. Since much of the T-SQL code one uses is deployed to a SQL Server instance as database objects (stored procedures, functions, etc.), the original code files are not managed or even discarded. However, for reusable utility code that is often reused, there should be an easy-to-use storage system to maintain those T-SQL code assets.

Think for a minute about some of the T-SQL code assets you have. Where are those stored? Are they portable? Are they organized in an easy-to-understand manner? When I think about assets like that, my collection of T-SQL goodies comes to mind. I’ve got a sizeable set of scripts that I’ve collected over the years that have templates for just about everything I need, from finding large tables to looking for potential undefined relationships between tables. I use those scripts frequently, and occasionally update or add to the collection. For a while, I just kept them in a Dropbox folder, and would manually navigate to that folder when I needed to get one of the scripts. It was a clunky but usable process.

SQL Server Management Studio Projects

When I discovered SSMS projects, I changed my whole approach to T-SQL code management. Management Studio allows the creation of solutions and projects much like those in Visual Studio, so it’s a very comfortable experience. Under the hood, the T-SQL files are just stored in the file system in a common folder per project, as is the metadata for each connection used in that project.

Creating a new project is done through the SSMS menu by drilling to File –> New –> Project.

The configuration of a project is very simple: give it a name, point to the directory location, and specify the solution settings (more on solutions in just a moment). The above will create an empty project, into which we can start adding new or existing T-SQL code files.

To view or manage project assets, you’ll need to use the Solution Explorer window. If it’s not already shown, you can access this window through the View –> Solution Explorer menu selection.

The above is a simple project containing two source files. The source code files are created by right-clicking on the Queries folder and selecting New Query. You can also add existing code files into this project by right-clicking the project and selecting Add –> Existing Item. Note that the latter will create a copy of the specified script into the current project folder, leaving the original code file intact.

Using Solutions

SSMS projects are organized in solutions. As shown above, creating a new project will automatically create a new solution, which will have the same name as the project by default. Solutions don’t have any functional impact on the code; they simply allow for a logical grouping of related projects.

Each solution can contain multiple projects. Adding a new project to an existing solution can be done by opening the project and navigating to File –> Add –> New Project. Multiple projects will show up as virtual subfolders beneath the solution.

Conclusion

SQL Server Management Studio projects make T-SQL code management simpler. By grouping related code files into projects, and further grouping related projects into solutions, managing SQL code evolves from a folder dump into a more sophisticated and organized structure that is more portable, logical, and easier to manage in source control.