I’ve just finished promised SSMS add-in which enables creating of custom folders directly in SSMS Object Explorer. Idea for add-in came from fact that I was dealing with hundreds of SQL objects and was fed up of browsing for correct one in SSMS standard folders. I tried also many “quick find” add-ins but they were not 100% solution for this issue.

I observed that I search for object in two ways:

• I know full or part object’s name – I use free Red Gate’s SQL Search Add-In (unfortunately, I have issue with integrating it with my add-in now). To make long story short, you can find any object very quickly with this add-in.
• I can’t remember any part of object’s name  – I was lost at this point and doomed to browsing for object in standard e.g. Stored procedure node. 

Second of mentioned points was the key driver to develop this add-in for me. I’ve even found similar commercial solution (for $65) but it supports only one level hierarchy. I employed SQL object schemas for emulating one level hierarchy before and it was simply not enough for me.

My Add-in currently supports following:

• Create custom folders in SSMS’s object explorer using drag&drop. It works for databases, stored procedures, functions, views and tables
• Ctrl+click on object will bring ALTER script
• Perform bulk operations in explorer-style management dialog – I did that because while I was refactoring and re-foldering more objects, drag&drop was not enough

See this short video or following screenshot to find how it works:

Technical solution

I must say that it was really hard to develop this add-in because documentation for SSMS add-in development is really bad. Add-In is developed in C# and partly in C++ and is installable to SQL Server Management Studio and supports SQL Server 7.0 and higher versions on server-side. From client point of view, it supports currently only SQL Server 2008 R2 Client Tools because this is platform I am working on these days. My intention was to support also lower versions of client tools but I soon found out that Microsoft made incompatible changes in 2005, 2008 and 2008 R2. I did first version for me and my team members so there was no push to convert it to other platforms. If I feel pressure on different platforms I will probably convert it. You can push me for other supported platforms here.

Logic of handling folders is quite simple:

If you create empty folder within standard SSMS node nothing happened on server-side, it only creates folder in client tree within Object Explorer. This is the reason why empty folders are not persistent. After you drag anything to folder, extended property is created/updated on dragged SQL object (e.g. table) by sp_addextendedproperty / sp_updateextendedproperty procedures.

If you expand standard tree node (e.g. Stored procedures), every object inside is queried with for extended properties by fn_listextendedproperty procedure. Add-in than creates folder structure dynamically based on found custom extended properties inserted by add-in before. Based on this internal behavior, you can bypass add-in when you create extended properties for your objects on your own – add-in just reads them and creates folders based on their values.

Synergy with Red Gate’s products

We are using ingenious Red Gate’s SQL Source Control SSMS Add-In which handles database versioning for our team (in Subversion). I used extended properties as “persistence medium” for folders because I wanted to share logical folders in our databases across all team members. My add-in and Red Gate’s add-in together works like a charm for that – when I create folder in my dev database and commit changes to source control, other team member see this folder once he updates his development database from source control. This is because extended properties are versioned as well.

I am big fan of Red Gate’s SQL Search and SQL Prompt add-ins so I examined coexistence of those to add-ins with mine. SQL Prompt is ok, no issues. Unfortunately my add-in cannot currently handle situation when SQL Search add-in is jumping to Object Explorer treeview for highlighting object (it crashes). I was trying hard to solve that but I will probably ask Red Gate for changing their SQL Search code because I simply cannot do that on my side. They will probably send me to ****** but I will try

Roadmap

Roadmap is maybe strong word but here is a list of features which came from every day use of this add-in:

• ability to change color of folders – this will faster catch your eye for correct folder
• have one folder “Uncategorized” which would serve as container for unfoldered objects
• ability to completely bypass standard SSMS structure (e.g. combine tables, stored procedures and function in one folder name “Banking Module”)
• store custom queries in your folders
• … and some more stuff

You can download add-in on my pages.

I hope it will be helpful.

Tags: Add-In, custom folders, development, Red Gate, refactoring, server management, SQL, SQL Prompt, SQL Server, SSMS