Thursday, April 19, 2012

Today's free product code

Today's free license code will appear on one of the product pages at 12 noon (valid for that product only). As explained in the email the code will appear at the end of the product description at the top of the product page.  Time zone: (UTC-05:00) Eastern Time (US & Canada)

Wednesday, April 18, 2012

My database is a jungle, I need an intelligent search engine

Thousands of stored procedures and no good way to find the one you are looking for, so what do you do? You write your own stored procedure thus, not only wasting valuable hours but also contributing into increasing the “density of the jungle” making it even harder to find anything in the future. Multiply this by 260 days a year and 10 other people like you and you get the picture. My guess is that if one would do a complete inventory of the stored procedures in a database would likely find a lot of repetition – multiple stored procedures created by different people at different times doing exactly the same thing.

What if a google type search engine could take queries like “get all north-west region customers that purchased product  x during a given period of time”, search through the database schema and return all the stored procedures that potentially answer this query as well as the list of tables that would potentially be involved in answering such query? That would be beautiful, wouldn’t it?

However, the search engine would need some help to be able to do this. Adding extended properties was a great step in the right directions - at least someone that is diligent enough has a way to tag the objects with keywords and phrases that can later be used to efficiently locate those objects.  I would argue that it would be beneficial if there were at least two predefined mandatory extended properties: “description” and “keywords”. I know that many DBAs and developers might find such enforcement a bit annoying but in the long run I think most of them will be thankful.

Related products/tools:

SSMS - lets you filter objects based on the name, schema, owner and creation date but it does not provide for filtering based on the extended properties or the definition of the object. However, you can always query the system tables - not ideal but better than nothing.

xSQL Documenter:  will document the extended properties and the object definitions so you can technically go into the documentation and search for an object.  The drawback is that the documentation becomes outdated soon after it is generated. Of course you can refresh it but that means more time.

Object Search:  a free tool that allows you to search in the definition of the object using a regular expression as the matching criteria. Current version does not look into the extended properties.