Sorting SQL Project Files in SQL Server Management Studio

Author	Jasper Smith	Hits	8932
Create Date	16-05-2006	Last Updated	20-03-2007
Versions	SQL2005	Feedback	Provide feedback on this article

Overview

When you have a lot of SQL files in a SQL Serrer Management Studio (SSMS) Project or a lot of Packages in a SSIS Project, it can become difficult to find the file or packages you are looking for as the order that they are displayed in the Solution Explorer is the order they were added to the project. The utility described in this article allows you to add menu items to sort the SQL files in a SSMS Project or packages in an SSIS project. It does this by modifying the project file (which is in xml format) and sorting the elements using an XSL Transform. It iterates through all projects in the currently open solution and sorts the SQL Files.

Download SortSQLFilesInProject (1.0.0.2)
Download SortSQLFilesInProject Source Files(1.0.0.2)

Using SortSQLFilesInProject

To get started with SortSQLFilesInProject simply follow the instructions below to setup the required commands and parameters. Make sure that you have saved the current solution before trying to sort the files.Because SSMS/VS will detect that the project file has modified external to itself, it will prompt you to Reload the project after sorting. Simply click on the Reload button to view the sorted results. If you have multiple projects in your Solution, you will be prompted to Reload each project individually. The instructions are exactly the same for SSMS or Visual Studio.

Unzip the SortSQLFilesInProject.zip file into a folder (in the example on the right I have unzipped it to C:\SSMSUtilities)
In SQL Server Management Studio, Select the External Tools item from the Tools Menu and you will see a dialog box similar to the one on the right of this page
Fill out the dialog as indicated in the image on the right (Modify the Command to point to the folder where you unzipped the SortSQLFilesInProject.exe executable)
Make sure you select the Use Output Window checkbox
Click Add and repeat in order to add the descending sort and make sure to change the Arguments from "$(SolutionFileName) Asc" to "$(SolutionFileName) Desc"
Close the dialog by clicking OK and you should now see two new options in the Tools Menu (im my case they are called SortFiles Asc and SortFiles Desc) which you can use to sort SQL files in projects.

To add buttons to the SSMS toolbar for these commands simply select the Customize item from the Tools menu in SSMS

Select Tools in the Categories List
Select External Command 1 in the Commands list
Select and drag External Command 1 onto a tool bar
Right click on the new External Command 1 button and edit the Name property to SortFiles Asc
Repeat for External Command 2

Note for solutions using Visual Source Safe : When sorting files in solutions that are under source control, the entire solution needs to be checked out. Once the sort is complete, the solution may need to be closed and then reopened before being checked in otherwise an error will be encountered. When reopening the new newly sorted solution, you will be prompted to add the project files back into sourcesafe as the bindings are lost. Click the use solution binding button on the message prompt to add the project files back into source safe. You will now be able to check all files back into Source Safe.

Revision History

Author	Date	Version	Description
Jasper Smith	02-11-2005	1.0.0.0	Initial release
Jasper Smith	16-05-2006	1.0.0.1	Added ability to sort SSIS packages in a project
Jasper Smith	20-03-2007	1.0.0.2	Improved handling when solution under source control

Download SortSQLFilesInProject (1.0.0.2)
Download SortSQLFilesInProject Source Files(1.0.0.2)