Improving Redgate SQL Search: Some Development Points

I love and use the Redgate utilities, including the free Redgate SQL Search, when working in the SQL Server Management Studio.
Everything would be fine, but I still dreamed of a sane tool that would allow you to create your own folders in the object tree and put labels / procedures in them.
At one point, such a one appeared - SQLTreeO , but then I came across a problem - SQLSearch does not search in the folders it created.



Actually, the small addin for the addin started with this - SQLSearch Extender . Further - more, once such a binge went, then why not add as much functionality as Redgate is not in a hurry to implement the wishes of users .
What is added to the SQLSearch functionality (settings in Tools -> SQL Search Extender menu):

• Search in folders created by SQLTreeO
• Regexp search
• Code highlighting and line numbering, navigation through entries
• Shows table and CLR UDF scripts
• Filter to exclude from searches by object name (for example, sp_MS%,% __ temp, etc.)
• ... all sorts of extra chips

')
Actually, notes on the development.
The API for Microsoft SSMS does not advertise, the following articles helped a lot at the start -
SQL Server Management Studio 2005 Add In and SQL 2008r2 breaks ssms addins

Almost all that is necessary for happiness, these articles are told, I will note a few points

The moment 1. We catch window SQLSearch.

Since we have practically no information initially, we hang the handler on Connect.OnCmdUIContextChanged and recall WinAPI EnumChildWindows. With deep inner satisfaction, we find that within the desired window lies the heir from UserControl, then by type and name ( Hawkeye to help) we find the elements we need. Further we do everything that it is necessary for us with the interface.

The moment 2. Search in folders SQLTreeO

The idea is simple - after running the SQLSearch search, we look at the element selected in the Object Explorer and, if its name does not match the one we need, then we go up to the server node level and run through the folders.

The moment 3. Syntax highlighting.

Scripts SQLSearch displays in standard RichEdit. I really wanted to cover it from above with the editor from the studio itself (a random link for keywords), it almost worked out, but I didn’t have the patience to investigate and eliminate all sorts of minor troubles.
As a result, I took the path of least resistance, hiding it behind my RichEdit and remembering the existence of Microsoft.SqlServer.SqlParser.Parser and, for speed, forming RTF handles.

Moment 4. I want a beautiful icon in the Tools menu!

No matter how funny it is, but if this is done without problems in the drop-down context menus, in this case I did not find the information, I had to tinker - go to System.Windows.Forms.AxHost, IPictureDisp and conjure with masks.

Moment 5. I want to search regexp'ami!

At first, I was saddened, because I did not see a way to do this without modifying SQLSearch itself, but then a solution was found.
SQLSearch for caching data uses SQLite and .NET wrapper for it System.Data.SQLite , the library is actually loaded at the moment when we start the search. But since we are in the same domain and catch the window before the search begins, we can quickly load our SQLite library. Download the System.Data.SQLite sources, make sure that the version number and key match the one used by SQLSearch itself, and modify the System.Data.SQLite.SQLiteCommand, catching sql queries and modifying the ones you need.
Implementing the search itself with regexps was easy - just implement a class in your library, marking it with the SQLiteFunction attribute - SQLite REGEXP function , after which we can write all sorts of things like select * from table where text REGEXP '.*GOOD\Z'

The same hack helped remove the limit on the maximum number of search results - SQLSearch limits the output to 150 results.

The moment 6. Reception of scripts of objects.

It's simple - helps Microsoft.SqlServer.Management.Smo

In the plans, just4fun, I want to decompile and show the CLR UDF source files using the related .NET Reflector , but for now there are problems related to the fact that

• addin itself should use a version of the .NET Framework no older than 3.5 (SSMS requirement), and .NET Reflector should not use 4.0 under. (we go around through COM ...)
• it is impossible to call a COM object written under .NET from .NET (it costs ...)
• and after all this was defeated and worked in the test version, when launched from SSMS, it drops from COM error 8004003.

Written under MS Visual Studio 2010, .NET framework 3.5 and tested under Microsoft SQL Server Management Studio 10.0.1600.22 (SQL Server 2008R2)

The source itself is not yet spread, but the library is not closed by anything, the amount of code is small and, if there is a reflector, anyone can see the implementation.

References:

Download
The page SQLSearchExtender on the side of the book
"Discussion" on the SQLTreeO forum

Upd 2012.06.27
Fixed crash under SSMS v.10.50. *

In the meantime, the point is, on the Redgate site, SQLSearch has been updated from version 1.1.4.3 to 1.1.6.1 and [FIXED], the hack with SQLite substitution on the fly now does not work.
It helps to copy x: \ Program Files (x86) \ SQLSearch2SQLTreeO \ SQLite.Interop.dll and x: \ Program Files (x86) \ SQLSearch2SQLTreeO \ System.Data.SQLite.dll to "x: \ ProgramData \ Red Gate \ SQL Search \ Active "and" x: \ ProgramData \ Red Gate \ SQL Search \ Source ". Here is such a bad luck, I will understand.