New version of SQLTreeo SSMS Add-In was released. We fixed few bugs and include also one major change – SQL Server 2012 support. SQLTreeo Add-In also became commercial product which will help us to give more attention to further development and support. We are now working on local storage support which was desperately demanded by almost everybody who used SQLTreeo in the past. However we will still support current way of storage using extended properties. You can download new version here.Tags: Add-In, databases, development, folders, grouping, SQL, SQL Server, SSMS, SSMS Add-In, treeo
Update for lazy readers:
Short summary and download here.
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:
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 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
Keeping your SQL objects’ naming rules during furious development is hard. New levels of information are added continuously, entities are being splitted, then joined together again, entities are often renamed several times just because you have better name for them etc. For me, renaming is heart of T-SQL refactoring. But whenever you need to rename stored procedure which is referenced by many others, you have no built-in options how to do that. If you are in Visual Studio .NET and have ReSharper installed, it’s piece of cake. You just click rename, and it’s safely done throughout your solution.
You have one painful “workaround” in T-SQL (as far as I know) – to script all dependencies and perform careful search and replace operation and re-create all affected objects. I personally hated that because when
I was refactoring I wanted to think only about proper new names, not about anything else. But if you’re renaming frequent word which is used in not only in object name (but in column names etc.), you’re forced to walk through hundreds of lines and thinking If I should rename this or that.
On my current project, we started to use SQL Prompt SSMS add-in many months ago but I discovered it’s great value for T-SQL refactoring yesterday. It provides feature which is invaluable for described refactoring scenarios. This features is called “Smart Rename” and provides consistent renaming of SQL object even with renaming it in all dependencies.
Saved me hours this week.Tags: dependencies, development, Red Gate, refactoring, Rename, SQL, SSMS, t-sql